How to display the function procedure triggers source code in postgresql

Question

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

User · Answer

Here are few examples from PostgreSQL-9 5  Display list    Functions   df  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

User · Answer

df  in psql gives you the sourcecode

User · Answer

sf function name in psql yields editable source code of a single function   From https   www postgresql org docs 9 6 static app-psql html       sf    function description   This command fetches and shows the definition of the named function  in the form of a CREATE OR REPLACE FUNCTION command        If   is appended to the command name  then the output lines are numbered  with the first line of the function body being line 1

User · Answer

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  gt   df                                                         List of functions  Schema           Name           Result data type                Argument data types                  Type   -------- ---------------------- ------------------ ------------------------------------------------ --------  public   pg buffercache pages   SETOF record                                                        normal   skytf  gt   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

User · Answer

Slightly more than just displaying the function  how about getting the edit in-place facility as well    ef  lt function name gt  is very handy  It will open the source code of the function in editable format  You will not only be able to view it  you can edit and execute it as well   Just  ef without function name will open editable CREATE FUNCTION template   For further reference -  https   www postgresql org docs 9 6 static app-psql html

User · Answer

There are many possibilities  Simplest way is to just use pgAdmin and get this from SQL window  However if you want to get this programmatically then examinate pg proc and pg trigger system catalogs or routines and triggers views from information schema  that s SQL standard way  but it might not cover all features especially PostgreSQL-specific   For example   SELECT     routine definition  FROM     information schema routines  WHERE     specific schema LIKE  public      AND routine name LIKE  functionName

User · Answer

Since Version  psql  9 6 17  server 11 6   I have tried all of above answer but For me  postgres  gt   sf jsonb extract path text CREATE OR REPLACE FUNCTION pg catalog jsonb extract path text from json jsonb  VARIADIC path elems text     RETURNS text  LANGUAGE internal  IMMUTABLE PARALLEL SAFE STRICT AS  function jsonb extract path text function     postgres  gt   df  ERROR   column p proisagg does not exist LINE 6    WHEN p proisagg THEN  agg                   HINT   Perhaps you meant to reference the column  p prolang     df seems not working for me

User · Answer

additionally to  franc s answer you can use this from sql interface   select      prosrc from pg trigger  pg proc where  pg proc oid pg trigger tgfoid  and pg trigger tgname like   lt name gt      taken from here  http   www postgresql org message-id Pine BSF 4 10 10009140858080 28013-100000 megazone23 bigpanda com

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

Examples related to postgresql