Обсуждение: Quering complete PLPGSQL code
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?
Regards,
Néstor
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
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
Thanks a lot that worked!!!
On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
pg_get_functiondef(oid)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?
>
> Regards,
>
> NÊstor
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
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
![](https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif)
On Mon, Jul 14, 2014 at 8:07 PM, Néstor Boscán <nestorjb@gmail.com> wrote:
Thanks a lot that worked!!!On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers <gsievers19@comcast.net> wrote:pg_get_functiondef(oid)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?
>
> Regards,
>
> NÊstor
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
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
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:
Thanks a lot that worked!!!On Mon, Jul 14, 2014 at 6:53 PM, Jerry Sievers <gsievers19@comcast.net> wrote:pg_get_functiondef(oid)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?
>
> Regards,
>
> NÊstor
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800