Re: Result sets from functions

Hello

2012/4/16 Liam Caffrey <liam(dot)caffrey(at)gmail(dot)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.
>