Thread: Result sets from functions

Hello

2012/4/16 Liam Caffrey <liam.caffrey@gmail.com>:
> Hi,
>
> There is a feature that I have used in SQL Server which I find really useful
> for debugging (without using a debugger!!).
> It is this.... I can write multiple "select * from some_table" statements
> throughout my stored procedure (here read "pgsql function") and the
> individual result sets get "returned" to the results pane. This is usually
> to look into the contents of temp tables or intermediate stages. This is
> especially useful when debugging triggers.
>
> I cannot find something similar yet in Postgres. The "return query select *
> from some_table" doesn't do it for me but I have to fiddle with the RETURN
> value of the function definition which I don't want to do.
>
> Does anything like this exist? Could anybody recommend an equally effective
> strategy?

There is nothing similar in Postgres. But you can use following routine -

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
begin
  perform debug_set('select * from x');
  return 10;
end;
$function$

postgres=> \sf debug_set
CREATE OR REPLACE FUNCTION public.debug_set(query text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare r record;
begin
  for r in execute $1
  loop
    raise notice '%', r;
  end loop;
end;
$function$

postgres=> \set VERBOSITY terse
postgres=> select foo();
NOTICE:  (10,20)
NOTICE:  (30,40)
 foo
-----
  10
(1 row)

attention on sqlinjection

regards

Pavel Stehule

>
> Regards
>
> Liam Caffrey
>
> PS: Posted earlier to plsql-hackers which I think was the wrong place.
> Sorry. Still looking for feedback. Tnx.
>

On 16 April 2012 09:24, Liam Caffrey <liam.caffrey@gmail.com> wrote:
> Hi,
>
> There is a feature that I have used in SQL Server which I find really useful
> for debugging (without using a debugger!!).
> It is this.... I can write multiple "select * from some_table" statements
> throughout my stored procedure (here read "pgsql function") and the
> individual result sets get "returned" to the results pane. This is usually
> to look into the contents of temp tables or intermediate stages. This is
> especially useful when debugging triggers.
>
> I cannot find something similar yet in Postgres. The "return query select *
> from some_table" doesn't do it for me but I have to fiddle with the RETURN
> value of the function definition which I don't want to do.
>
> Does anything like this exist? Could anybody recommend an equally effective
> strategy?

You can use RAISE info and RAISE notice to write session output to a log-file.

I'm fairly confident that if you also set session parameters for
logging, you could capture the messages from just your session in a
separate file or pipe. I'd try this in psql first, but if that works
(I expect it to) any client could do something like that.

Of course, since the logging happens on the server, you'd at least
need the right permissions to write to the file or pipe ;)

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Mon, Apr 16, 2012 at 2:24 AM, Liam Caffrey <liam.caffrey@gmail.com> wrote:
> Hi,
>
> There is a feature that I have used in SQL Server which I find really useful
> for debugging (without using a debugger!!).
> It is this.... I can write multiple "select * from some_table" statements
> throughout my stored procedure (here read "pgsql function") and the
> individual result sets get "returned" to the results pane. This is usually
> to look into the contents of temp tables or intermediate stages. This is
> especially useful when debugging triggers.
>
> I cannot find something similar yet in Postgres. The "return query select *
> from some_table" doesn't do it for me but I have to fiddle with the RETURN
> value of the function definition which I don't want to do.
>
> Does anything like this exist? Could anybody recommend an equally effective
> strategy?

Unfortunately lack of proper stored procedures is single biggest thing
you'll miss if coming from SQL server.  Of course, functions can do
many things procedures can't but the limitations can be annoying for
certain use cases.

If your returned sets aren't too terribly huge, one possible
workaround is to return composite arrays:

create function get_sets(foos out foo[], bars out bar[]) returns record as
$$
begin
  select array(select f from foo f), array(select b from bar) into foos, bars;
end;
$$ language plpgsql;

as long as you have some means of dealing with arrays on the
client-side (what are you using to receive the data?) then you can do
things pretty efficiently that way.

merlin

On 2012-04-16, Liam Caffrey <liam.caffrey@gmail.com> wrote:
>
> Hi,
>
> There is a feature that I have used in SQL Server which I find really
> useful for debugging (without using a debugger!!).

> It is this.... I can write multiple "select * from some_table" statements
> throughout my stored procedure (here read "pgsql function") and the
> individual result sets get "returned" to the results pane. This is usually
> to look into the contents of temp tables or intermediate stages. This is
> especially useful when debugging triggers.

postgres doen't have stored procedure, only functions and there's only
oe way to return values from a function.

> I cannot find something similar yet in Postgres. The "return query select *
> from some_table" doesn't do it for me but I have to fiddle with the RETURN
> value of the function definition which I don't want to do.
>
> Does anything like this exist? Could anybody recommend an equally effective
> strategy?

RAISE NOTICE, (or raise log, raise debug, etc)

it only retiurns one row at a time and only to the dianostics stream
not to the data stream.

if you want to return a whole table or a whole query result set you'll
need to use a loop that repeatedly does RAISE...

RAISE
http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html

FOR LOOP
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

SET CLIENT_MIN_MESSAGES
http://www.postgresql.org/docs/current/static/sql-set.html
http://www.postgresql.org/docs/current/static/runtime-config-logging.html


--
⚂⚃ 100% natural