How to get COMPLETE function definition script in postgresql?

Question

I'm trying to get FULL definition text of a function, but the query that everyone suggests returns the functions script without the header and footer parts.

Here's what I'm using:

select prosrc from pg_proc where proname='my_func';

Here's what is returned:

DECLARE
some_var1 TEXT;
some_var2 TEXT;
Status character varying;

BEGIN
--SCRIPT CODE HERE

RETURN retval;
END;

But what I want is this:

CREATE OR REPLACE FUNCTION my_func(logdate_utc date)
RETURNS character varying
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
AS $BODY$
DECLARE
some_var1 TEXT;
some_var2 TEXT;
Status character varying;

BEGIN
--SCRIPT CODE HERE

RETURN retval;
END;
$BODY$;

ALTER FUNCTION my_func(date)
    OWNER TO some_owner;

Searched around and haven't found much. How can I get that result?

Community · Accepted Answer · 2020-06-20 09:12:55Z

12

Using SQL:

 select pg_catalog.pg_get_functiondef('my_func(date)'::regprocedure::oid);

Doc

Note that you should to use regprocedure type if you specify the parameters list and regproc type if you specify only function name (like 'my_func'::regproc::oid)

And using command line psql with \sf meta-command:

This command fetches and shows the definition of the named function, in the form of a CREATE OR REPLACE FUNCTION command. The definition is printed to the current query output channel, as set by \o.

The target function can be specified by name alone, or by name and arguments, for example foo(integer, text). The argument types must be given if there is more than one function of the same name.

from the documentation.

For example:

psql -c '\sf my_func(date)'

And to retrieve function's owner name you can use next query:

select proowner::regrole
from pg_proc
where oid = 'my_func(date)'::regprocedure;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 9, 2018 at 0:14

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

klin · Accepted Answer · 2018-02-09 00:26:11Z

9

Use pg_get_functiondef(func_oid):

SELECT pg_get_functiondef('my_func'::regproc);

answered Feb 9, 2018 at 0:26

klin

123k15 gold badges240 silver badges262 bronze badges

Comments

Bhavesh Kuvar · Accepted Answer · 2022-06-22 05:42:42Z

1

SELECT pg_get_functiondef(( SELECT oid FROM pg_proc where proname= 'my_func'));

answered Jun 22, 2022 at 5:42

Bhavesh Kuvar

112 bronze badges

1 Comment

lemon Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to get COMPLETE function definition script in postgresql?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related