How to display the complete code of a stored procedure?

Question

How do you view a stored procedure / function?

Let's say I have an old function without an original definition - I want to see what it does in pg / psql, but I cannot figure out how to do this.

using Postgres version 8.4.1

Answer 1

use pgAdmin or use pg_proc to get the source of your stored procedures. pgAdmin does the same.

Answer 2

\df+ <function_name> in psql .

Answer 3

\ef <function_name> in psql. This will give the whole function with editable text.

Answer 4

 SELECT prosrc FROM pg_proc WHERE proname = 'function_name'; 

This tells the function handler how to call the function. This may be the actual function source code for the interpreted languages, a link symbol, a file name, or something else, depending on the language / usage agreement

Answer 5

Use \df to list the entire stored procedure in Postgres.

Answer 6

If someone is wondering how to quickly query the directory tables and use the pg_get_functiondef() function here is an example query:

 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 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 n.nspname~'<$SCHEMA_NAME_PATTERN>' AND proname~'<$FUNCTION_NAME_PATTERN>' 

Answer 7

Usually you use a database manager application, for example pgAdmin , look at the object you are interested in and correctly click your path to the "script how to create" or similar.

Are you trying to do this ... without a management application?