Quering complete PLPGSQL code

N�stor Bosc�n <nestorjb@gmail.com> writes:

Hi

I want to get the PLPGSQL code from the PostgreSQL 9.1 database. I've used pg_proc that only gives me the body of the code. Is there a Postgres function that can build
all the code?

pg_get_functiondef(oid)

Regards,

Néstor

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 7/14/2014 3:33 PM, Néstor Boscán wrote:

I want to get the PLPGSQL code from the PostgreSQL 9.1 database. I've
used pg_proc that only gives me the body of the code. Is there a
Postgres function that can build all the code?

pg_dump can do that... to see how to do it yourself, you'll need to
look at the pg_dump code, its fairly complex.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks a lot that worked!!!

On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

What's really strange is that the function is returning the code like this:

FUNCTION public."PRUEBA_FUNCION2"(p_1 integer, OUT p_2 integer, INOUT p_3
integer, VARIADIC p_4 integer[])
RETURNS SETOF record
LANGUAGE plpgsql
IMMUTABLE STRICT SECURITY DEFINER ROWS 200
AS $function$begin
null;
end;$function$

So the LANGUAGE, INMUTABLE, STRICT, options are written before the code
instead of after the code.

Regards,

Néstor

On Mon, Jul 14, 2014 at 8:07 PM, Néstor Boscán <nestorjb@gmail.com> wrote:

What's really strange is that the function is returning the code like this:

FUNCTION public."PRUEBA_FUNCION2"(p_1 integer, OUT p_2 integer, INOUT p_3
integer, VARIADIC p_4 integer[])
RETURNS SETOF record
LANGUAGE plpgsql
IMMUTABLE STRICT SECURITY DEFINER ROWS 200
AS $function$begin
null;
end;$function$

So the LANGUAGE, INMUTABLE, STRICT, options are written before the code
instead of after the code.

Regards,

Néstor

On Mon, Jul 14, 2014 at 8:07 PM, Néstor Boscán <nestorjb@gmail.com> wrote:

On 07/14/2014 07:15 PM, Néstor Boscán wrote:

What's really strange is that the function is returning the code like this:

FUNCTION public."PRUEBA_FUNCION2"(p_1 integer, OUT p_2 integer, INOUT
p_3 integer, VARIADIC p_4 integer[])
RETURNS SETOF record
LANGUAGE plpgsql
IMMUTABLE STRICT SECURITY DEFINER ROWS 200
AS $function$begin
null;
end;$function$

So the LANGUAGE, INMUTABLE, STRICT, options are written before the code
instead of after the code.

That is acceptable. In fact that is what you see if you use \ef in psql:

CREATE FUNCTION ( )
RETURNS
LANGUAGE
-- common options: IMMUTABLE STABLE STRICT SECURITY DEFINER
AS $function$

$function$

Regards,

Néstor

On Mon, Jul 14, 2014 at 8:07 PM, Néstor Boscán <nestorjb@gmail.com
<mailto:nestorjb@gmail.com>> wrote:

Thanks a lot that worked!!!

On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers
<gsievers19@comcast.net <mailto:gsievers19@comcast.net>> wrote:

Néstor Boscán <nestorjb@gmail.com <mailto:nestorjb@gmail.com>>
writes:

Hi

I want to get the PLPGSQL code from the PostgreSQL 9.1

database. I've used pg_proc that only gives me the body of the
code. Is there a Postgres function that can build

all the code?

pg_get_functiondef(oid)

Regards,

NÊstor

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
<mailto:postgres.consulting@comcast.net>
p: 312.241.7800 <tel:312.241.7800>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general