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

\df+ <function_name> in psql.

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

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

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

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

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

Since PostgreSQL 9.1 \sf is available.

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.

We can use pg_get_functiondef() command to get the full function code of proc. Also creating the macro using the below code we can use it as shortcut key to get the function code. Try this method to display full postgresql fundtion code.

DO
$$
DECLARE	
        v_Inputtext	TEXT;
        v_output	text;
BEGIN
       v_Inputtext 	:= (SELECT lower('procedurename'));
       v_output 	:= (SELECT pg_get_functiondef(oid) FROM pg_catalog.pg_proc WHERE proname = v_Inputtext);
       RAISE NOTICE '%',v_output;
END;
$$

https://www.novicetechie.com/2020/01/how-to-display-full-postgresql-function.html

SELECT pg_get_functiondef(( SELECT oid 
                            FROM   pg_proc
                            WHERE  proname = 'function_name' ));

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?

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