Thread: 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

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

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