CODEWITHSUNDEEP
databasepostgresqlstored-proceduresplpgsqlfunction-definition
darren
darren

Reputation: 1613

How to show full definition of function or procedure?

How do you view a stored function or procedure?

Say I have an old function without the original definition - I want to see what it is doing, but I can't seem to figure out a way to do that.

Using Postgres version 8.4.1.

Upvotes: 161

Views: 215699

Answers (13)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

*In this answer, I introduce several ways to show the code of a function as shown below but you can also use these ways below to show the code of a procedure.

For example, you create my_func() function as shown below.

CREATE FUNCTION my_func(v1 INT, v2 INT) RETURNS INT
AS $$
BEGIN
  RETURN v1 + v2;
END;
$$ LANGUAGE plpgsql;

Then, you can show the code of my_func() with \sf as shown below:

postgres=# \sf public.my_func
CREATE OR REPLACE FUNCTION public.my_func(v1 integer, v2 integer)       
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN v1 + v2;
END;
$function$

postgres=# \sf+ public.my_func
        CREATE OR REPLACE FUNCTION public.my_func(v1 integer, v2 integer
)
         RETURNS integer
         LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3         RETURN v1 + v2;
4       END;
5       $function$

*Memos:

  • + can show line numbers.

  • You can omit the schema public..

Or, you can show the code of my_func() with pg_get_functiondef() and the OID(Object identifier) of my_func() as shown below:

postgres=# SELECT pg_get_functiondef('public.my_func'::regproc);
                        pg_get_functiondef
-------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.my_func(v1 integer, v2 integer)+
  RETURNS integer                                                 +
  LANGUAGE plpgsql                                                +
 AS $function$                                                    +
 BEGIN                                                            +
   RETURN v1 + v2;                                                +
 END;                                                             +
 $function$                                                       +

(1 row)

*Memos:

  • You can omit the schema public..

Or, you can show only the body code of my_func() with [pg_proc][4] as shown below:

postgres=# SELECT prosrc FROM pg_proc WHERE proname = 'my_func';
      prosrc
-------------------
                  +
 BEGIN            +
   RETURN v1 + v2;+
 END;             +

(1 row)

postgres=# SELECT prosrc FROM pg_proc WHERE oid = 'public.my_func'::regproc;
      prosrc
-------------------
                  +
 BEGIN            +
   RETURN v1 + v2;+
 END;             +

(1 row)

Or, you can show only the code of my_func() with information_schema.routines as shown below:

postgres=# SELECT routine_definition FROM information_schema.routines WHERE routine_name = 'my_func';
 routine_definition
--------------------
                   +
 BEGIN             +
   RETURN v1 + v2; +
 END;              +

(1 row)

Upvotes: 2

Jeel
Jeel

Reputation: 141

\sf function_name

(don't put a semicolon at the end)

Upvotes: 10

Dzmitry Darashuk
Dzmitry Darashuk

Reputation: 525

jdbcTemplate.queryForList("SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE'", String.class);

Upvotes: 0

techStud
techStud

Reputation: 318

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

Upvotes: 5

Antonio Bardazzi
Antonio Bardazzi

Reputation: 3236

Since PostgreSQL 9.1 \sf is available.

Upvotes: 15

msciwoj
msciwoj

Reputation: 772

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

Upvotes: 14

Veeresh Digasangi
Veeresh Digasangi

Reputation: 65

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.

Upvotes: 0

Asha Koshti
Asha Koshti

Reputation: 2993

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

Upvotes: 197

Puneet Purohit
Puneet Purohit

Reputation: 1281

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

Upvotes: 19

Maxim
Maxim

Reputation: 1666

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

Upvotes: 95

Milen A. Radev
Milen A. Radev

Reputation: 62593

\df+ <function_name> in psql.

Upvotes: 298

Frank Heikens
Frank Heikens

Reputation: 127137

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

Upvotes: 25

annakata
annakata

Reputation: 75854

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?

Upvotes: -3

Related Questions