postgresql 如何显示完整的存储过程代码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3524859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to display full stored procedure code?
提问by darren
How do you view a stored procedure/function?
您如何查看存储过程/函数？
Say I have an old function without the original definition - I want to see what it is doing in pg/psql but I can't seem to figure out a way to do that.
假设我有一个没有原始定义的旧函数 - 我想看看它在 pg/psql 中做什么，但我似乎无法找到一种方法来做到这一点。
using Postgres version 8.4.1
使用 Postgres 8.4.1 版
采纳答案by Frank Heikens
回答by Asha Koshti
\ef <function_name>in psql. It will give the whole function with editable text.
\ef <function_name>在 psql 中。它将为整个功能提供可编辑的文本。
回答by Maxim
SELECT prosrc FROM pg_proc WHERE proname = 'function_name';
This tells the function handler how to invoke the function. It might be the actual source code of the function for interpreted languages, a link symbol, a file name, or just about anything else, depending on the implementation language/call convention
这告诉函数处理程序如何调用函数。它可能是解释语言的函数的实际源代码、链接符号、文件名或其他任何内容，具体取决于实现语言/调用约定
回答by Puneet Purohit
Use \dfto list all the stored procedure in Postgres.
使用\df列出了在Postgres所有的存储过程。
回答by msciwoj
If anyone wonders how to quickly query catalog tables and make use of the pg_get_functiondef()function here's the sample query:
如果有人想知道如何快速查询目录表并使用pg_get_functiondef()以下示例查询：
SELECT n.nspname AS schema
,proname AS fname
,proargnames AS args
,t.typname AS return_type
,d.description
,pg_get_functiondef(p.oid) as definition
-- ,CASE WHEN NOT p.proisagg THEN pg_get_functiondef(p.oid)
-- ELSE 'pg_get_functiondef() can''t be used with aggregate functions'
-- END as definition
FROM pg_proc p
JOIN pg_type t
ON p.prorettype = t.oid
LEFT OUTER
JOIN pg_description d
ON p.oid = d.objoid
LEFT OUTER
JOIN pg_namespace n
ON n.oid = p.pronamespace
WHERE NOT p.proisagg
AND n.nspname~'<$SCHEMA_NAME_PATTERN>'
AND proname~'<$FUNCTION_NAME_PATTERN>'
回答by Veeresh Digasangi
You can also get by phpPgAdmin if you are configured it in your system,
如果您在系统中配置了它，您也可以通过 phpPgAdmin 获取，
Step 1: Select your database
第 1 步：选择您的数据库
Step 2: Click on find button
第二步：点击查找按钮
Step 3: Change search option to functions then click on Find.
第 3 步：将搜索选项更改为功能，然后单击“查找”。
You will get the list of defined functions.You can search functions by name also, hope this answer will help others.
您将获得已定义函数的列表。您也可以按名称搜索函数，希望这个答案对其他人有帮助。
回答by Developer_Suraj
To see the full code(query) written in stored procedure/ functions, Use below Command:
要查看以存储过程/函数编写的完整代码（查询），请使用以下命令：
sp_helptext procedure/function_name
for function name and procedure name don't add prefix 'dbo.' or 'sys.'.
对于函数名和过程名，不要添加前缀“dbo”。或“系统”。
don't add brackets at the end of procedure or function name and also don't pass the parameters.
不要在过程或函数名称的末尾添加括号，也不要传递参数。
use sp_helptext keyword and then just pass the procedure/ function name.
使用 sp_helptext 关键字，然后只传递过程/函数名称。
use below command to see full code written for Procedure:
使用以下命令查看为程序编写的完整代码：
sp_helptext ProcedureName
use below command to see full code written for function:
使用以下命令查看为函数编写的完整代码：
sp_helptext FunctionName
回答by annakata
Normally speaking you'd use a DB manager application like pgAdmin, browse to the object you're interested in, and right click your way to "script as create" or similar.
通常来说，你会使用像pgAdmin这样的数据库管理器应用程序，浏览到你感兴趣的对象，然后右键单击你的方式来“创建脚本”或类似的。
Are you trying to do this... without a management app?
您是否正在尝试这样做...没有管理应用程序？
