How to display the function, procedure, triggers source code in postgresql?

How to print functions and triggers sourcecode in postgresql? please let me know if any one know the query to display the function, triggers source code.

Solution 1:

\df+ in psql gives you the sourcecode.

Solution 2:

For function:

you can query the pg_proc view , just as the following

select proname,prosrc from pg_proc where proname= your_function_name; 

Another way is that just execute the commont \df and \ef which can list the functions.

skytf=> \df           
                                             List of functions
 Schema |         Name         | Result data type |              Argument data types               |  Type  
--------+----------------------+------------------+------------------------------------------------+--------
 public | pg_buffercache_pages | SETOF record     |                                                | normal


skytf=> \ef  pg_buffercache_pages

It will show the source code of the function.

For triggers:

I dont't know if there is a direct way to get the source code. Just know the following way, may be it will help you!

• step 1 : Get the table oid of the trigger:

    skytf=> select tgrelid from pg_trigger  where tgname='insert_tbl_tmp_trigger';
      tgrelid
    ---------
       26599
    (1 row)

• step 2: Get the table name of the above oid !

    skytf=> select oid,relname  from pg_class where oid=26599;
      oid  |           relname           
    -------+-----------------------------
     26599 | tbl_tmp
    (1 row)

• step 3: list the table information

    skytf=> \d tbl_tmp

It will show you the details of the trigger of the table . Usually a trigger uses a function. So you can get the source code of the trigger function just as the above that I pointed out !

Solution 3:

Here are few examples from PostgreSQL-9.5

Display list:

1. Functions: \df+
2. Triggers : \dy+

Display Definition:

postgres=# \sf
function name is required

postgres=# \sf pg_reload_conf()
CREATE OR REPLACE FUNCTION pg_catalog.pg_reload_conf()
 RETURNS boolean
 LANGUAGE internal
 STRICT
AS $function$pg_reload_conf$function$

postgres=# \sf pg_encoding_to_char
CREATE OR REPLACE FUNCTION pg_catalog.pg_encoding_to_char(integer)
 RETURNS name
 LANGUAGE internal
 STABLE STRICT
AS $function$PG_encoding_to_char$function$