how to display full stored procedure code?

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.

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

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

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