Re: Result sets from functions

On Mon, Apr 16, 2012 at 2:24 AM, Liam Caffrey <liam(dot)caffrey(at)gmail(dot)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