postgresql postgresql中如何显示函数、过程、触发器源代码？

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

提问by ganesh

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.

如何在 postgresql 中打印函数和触发器源代码？如果有人知道显示功能的查询，请告诉我，触发源代码。

回答by Mateusz Grotek

\df+in psqlgives you the sourcecode.

\df+在psql 中为您提供源代码。

回答by francs

For function:

对于功能：

you can query the pg_proc view , just as the following

可以查询pg_proc视图，如下

select proname,prosrc from pg_proc where proname= your_function_name; 

Another way is that just execute the commont \dfand \efwhich can list the functions.

另一种方法是只执行commont\df并且\ef可以列出函数。

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:

• 步骤1：获取触发器的表oid：

    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 !

• step 2：获取上述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 !

它将显示表的触发器的详细信息。通常触发器使用一个函数。所以你可以像我上面指出的那样获得触发器函数的源代码！

回答by Sathish

Here are few examples from PostgreSQL-9.5

以下是来自 PostgreSQL-9.5 的几个例子

Display list:

显示列表：

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

1. 职能： \df+
2. 触发器： \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$

回答by Grzegorz Szpetkowski

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_procand pg_triggersystem catalogs or routinesand triggersviews from information schema (that's SQL standard way, but it might not cover all features especially PostgreSQL-specific). For example:

有很多可能性。最简单的方法是使用 pgAdmin 并从 SQL 窗口获取它。但是，如果你想获得这个程序然后examinatepg_proc和pg_trigger系统目录或routines与triggers从信息架构视图（这是SQL标准的方式，但它可能无法覆盖所有的功能尤其是PostgreSQL特有的）。例如：

SELECT
    routine_definition 
FROM
    information_schema.routines 
WHERE
    specific_schema LIKE 'public'
    AND routine_name LIKE 'functionName';

回答by mythicalcoder

Slightly more than just displaying the function, how about getting the edit in-place facility as well.

不仅仅是显示功能，还有如何获得就地编辑功能。

\ef <function_name>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.

\ef <function_name>非常方便。它将以可编辑的格式打开函数的源代码。您不仅可以查看它，还可以编辑和执行它。

Just \efwithout function_name will open editable CREATE FUNCTION template.

只是\ef没有 function_name 将打开可编辑的 CREATE FUNCTION 模板。

For further reference -> https://www.postgresql.org/docs/9.6/static/app-psql.html

进一步参考 -> https://www.postgresql.org/docs/9.6/static/app-psql.html

回答by Sergey Tarasov

\sffunction_name in psql yields editable source code of a single function.

\sfpsql 中的 function_name 生成单个函数的可编辑源代码。

From https://www.postgresql.org/docs/9.6/static/app-psql.html:

从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.

\sf[+] function_description 此命令以 CREATE OR REPLACE FUNCTION 命令的形式获取并显示命名函数的定义。

如果 + 附加到命令名称，则输出行被编号，函数体的第一行是第 1 行。

回答by Andreas Dietrich

additionally to @franc's answer you can use this from sql interface:

除了@franc 的回答之外，您还可以从 sql 界面使用它：

select 
    prosrc
from pg_trigger, pg_proc
where
 pg_proc.oid=pg_trigger.tgfoid
 and pg_trigger.tgname like '<name>'

(taken from here: http://www.postgresql.org/message-id/[e-mail])

（取自此处：http: //www.postgresql.org/message-id/[e-mail]）

回答by Ravi Parekh

Since Version: psql (9.6.17, server 11.6)

自版本：psql（9.6.17，服务器 11.6）

I have tried all of above answer but For me

我已经尝试了以上所有答案，但对我来说

postgres=> \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=> \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.

df 似乎对我不起作用。