Re: Result sets from functions - Mailing list pgsql-general

From Pavel Stehule
Subject Re: Result sets from functions
Date
Msg-id CAFj8pRDDP3gbioqqvPadCsXhknt-M--=M8rLVULd5zQETT_U7w@mail.gmail.com
Whole thread Raw
In response to Result sets from functions  (Liam Caffrey <liam.caffrey@gmail.com>)
List pgsql-general
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.
>

pgsql-general by date:

Previous
From: Jason Armstrong
Date:
Subject: Importing oracle data dump files to postgresql
Next
From: Roger Leigh
Date:
Subject: The scope of extensions