Re: Result sets from functions

On 2012-04-16, 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.

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