[postgresql] how to display full stored procedure code?

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.

using Postgres version 8.4.1

If anyone wonders how to quickly query catalog tables and make use of the pg_get_functiondef() function here's the sample 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
--      ,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>'

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

You can also get by phpPgAdmin if you are configured it in your system,

Step 1: Select your database

Step 2: Click on find button

Step 3: Change search option to functions then click on Find.

You will get the list of defined functions.You can search functions by name also, hope this answer will help others.

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

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.

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

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

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

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.

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

\df+ <function_name> in psql.