Thread: How can I display the contents of a function?

I would like to see what's in a function.  I can do this if I use pgAdmin III just by clicking on the function, and it appears in the SQL pane opposite.  But how is this done?  Is there a command like DESCRIBE FUNCTION logging.do_stuff; ?  And if possible, doing this without psql.

Thanks

Thom

Thom Brown a écrit :
> I would like to see what's in a function.  I can do this if I use pgAdmin
> III just by clicking on the function, and it appears in the SQL pane
> opposite.  But how is this done?  Is there a command like DESCRIBE FUNCTION
> logging.do_stuff; ?  And if possible, doing this without psql.
>

pgAdmin looks at the prosrc column of the pg_proc catalog. You can do
the same with this query:

  SELECT prosrc FROM pg_proc WHERE proname='your function name here';

Regards.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

In response to Thom Brown :
> I would like to see what's in a function.  I can do this if I use pgAdmin III
> just by clicking on the function, and it appears in the SQL pane opposite.  But
> how is this done?  Is there a command like DESCRIBE FUNCTION logging.do_stuff;
> ?  And if possible, doing this without psql.

All details about a function are stored in pg_proc. So you can do a
select on this table to retrieve the source-code.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Thom Brown wrote:
> I would like to see what's in a function.  I can do this if I use
> pgAdmin III just by clicking on the function, and it appears in the
> SQL pane opposite.  But how is this done?  Is there a command like
> DESCRIBE FUNCTION logging.do_stuff; ?  And if possible, doing this
> without psql.
>
> Thanks
>
> Thom
You can do
\df+ function_name(arg type, arg type...)
in psql

Thanks everyone!  I looked around the user-defined functions section of the documentation, but there wasn't any mention of this.  I'm sure others would find it useful if it were included, or at least referenced to.

Thom

Thom Brown wrote:
> I would like to see what's in a function.  I can do this if I use
> pgAdmin III just by clicking on the function, and it appears in the
> SQL pane opposite.  But how is this done?  Is there a command like
> DESCRIBE FUNCTION logging.do_stuff; ?  And if possible, doing this
> without psql.
>
> Thanks
>
> Thom
You can also do
\df+ function_name(args)
in psql

In response to Thom Brown :
> Thanks everyone!  I looked around the user-defined functions section of the
> documentation, but there wasn't any mention of this.  I'm sure others would
> find it useful if it were included, or at least referenced to.

Just for info:

wait for the upcoming new release 8.4, it contains a function called
pg_get_functiondef(). Example:

test=# create or replace function foo(in a int) returns int as $$begin return a*10; end; $$language plpgsql;
CREATE FUNCTION
test=# select pg_get_functiondef('foo'::regproc);
                pg_get_functiondef
--------------------------------------------------
 CREATE OR REPLACE FUNCTION public.foo(a integer)
  RETURNS integer
  LANGUAGE plpgsql
 AS $function$begin return a*10; end; $function$

(1 row)


http://developer.postgresql.org/pgdocs/postgres/functions-info.html


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Another reason to look forward to 8.4. :)

Does that function return information in an identical way to selecting from pg_proc?

In response to Thom Brown :
> Another reason to look forward to 8.4. :)
>
> Does that function return information in an identical way to selecting from
> pg_proc?

Apparently.




>
> 2009/1/19 A. Kretschmer <andreas.kretschmer@schollglas.com>

Please no fullquote below your text, thx.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net