CODEWITHSUNDEEP
postgresqlfunction
EricBlair1984
EricBlair1984

Reputation: 369

How to get COMPLETE function definition script in postgresql?

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?

Upvotes: 4

Views: 13606

Answers (3)

Bhavesh Kuvar
Bhavesh Kuvar

Reputation: 11

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

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

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;

Upvotes: 11

klin
klin

Reputation: 121474

Use pg_get_functiondef(func_oid):

SELECT pg_get_functiondef('my_func'::regproc);

Upvotes: 8

Related Questions