Re: Dump functions alone

Hello all- Is there a way to just dump functions in a schema in to a
txt file/ sql file ? Thank you.

You have two options.

- Use contrib module pg_extractor
https://github.com/omniti-labs/pg_extractor
- Use pg_proc catalog to get function definition

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP(at)oru(dot)com> wrote:

> Hello all- Is there a way to just dump functions in a schema in to a txt
> file/ sql file ? Thank you.****
>

One more thing you can also get it from pg_get_functiondef() system
function.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <
raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:

> You have two options.
>
> - Use contrib module pg_extractor
> https://github.com/omniti-labs/pg_extractor
> - Use pg_proc catalog to get function definition
>
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
> On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP(at)oru(dot)com> wrote:
>
>> Hello all- Is there a way to just dump functions in a schema in to a txt
>> file/ sql file ? Thank you.****
>>
>
>

Thank you. The PGExtractor is interesting! I was trying to get all the
function declaration and definition ( about 400+) by this method

pg_dump -Fc -v -s schemaname -f temp.dump yourdatabase

pg_restore -l temp.dump | grep FUNCTION >functionlist

pg_restore -L functionlist temp.dump >yourfunctions.sql

mentioned in
http://archives.postgresql.org/pgsql-general/2005-10/msg01633.php

I'll try this and your solutions as well

From: Raghavendra [mailto:raghavendra(dot)rao(at)enterprisedb(dot)com]
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Dump functions alone

One more thing you can also get it from pg_get_functiondef() system
function.

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra
<raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:

You have two options.

* Use contrib module pg_extractor
https://github.com/omniti-labs/pg_extractor
* Use pg_proc catalog to get function definition

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP(at)oru(dot)com> wrote:

Hello all- Is there a way to just dump functions in a schema in to a
txt file/ sql file ? Thank you.

Any help in getting function argument names is appreciated. Thank you

To dump the functions and their definitions , I first created a
pga_functions view as mentioned in one of the archives.

First Step: Create a pga_functions view

create or replace view pga_functions as

select

l.lanname as language,

n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)
||')' as name,

t.typname as returntype,

E'\n\n'||'CREATE OR REPLACE FUNCTION
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtyp
es)||E')\n'||' RETURNS '||t.typname||' AS'||E'\n$$' ||prosrc

/* (select case when lanname <> 'c'

then replace(prosrc,''',''') else replace(prosrc,''',''')||'.so'

end)*/||E'\n$$'||' LANGUAGE ''' || l.lanname || E''' VOLATILE;\n' as
source

from pg_proc p, pg_type t, pg_namespace n, pg_language l

where p.prorettype = t.oid and p.pronamespace = n.oid

and p.prolang = l.oid;

Second Step: Did a select from this view and dumped to a sql file

psql -d DATABASENAME -c "select source from pga_functions where name
like 'om%'"> omsfunctions.sql

Now in the file , not sure how to get the functions with argname or (not
sure if it's called the proargname)

Eg The original function is defined as

-- Function: oms.om_change_i_division(integer, text, text)

-- DROP FUNCTION oms.om_change_i_division(integer, text, text);

CREATE OR REPLACE FUNCTION oms.om_change_i_division(v_incidentid
integer, v_division text, v_olddivision text)

RETURNS void AS

$BODY$

DECLARE

v_Value TEXT;

v_OldValue TEXT;

v_Message TEXT;

BEGIN

v_Value := SUBSTR(v_Division,1,3);

v_OldValue := SUBSTR(v_OldDivision,1,3);

v_Message := 'Changed Incident Division to ' || coalesce(v_Value,'')
|| ' from ' || coalesce(v_OldValue,'');

update OM_Incidents

set

Division = v_Division

where

IncidentId = v_IncidentId;

PERFORM om_ins_audit(v_IncidentId,'Modified',v_Message);

RETURN;

END; $BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION oms.om_change_i_division(integer, text, text) OWNER TO
oru;

The output of a function with the above pg_functions view comes out as
below with no argument names.

-- Function: oms.om_change_i_division(integer, text, text)

-- DROP FUNCTION oms.om_change_i_division(integer, text, text);

CREATE OR REPLACE FUNCTION oms.om_change_i_division( integer, text,
text) (With no argument names)

RETURNS void AS

$BODY$

DECLARE

v_Value TEXT;

v_OldValue TEXT;

v_Message TEXT;

BEGIN

v_Value := SUBSTR(v_Division,1,3);

v_OldValue := SUBSTR(v_OldDivision,1,3);

v_Message := 'Changed Incident Division to ' || coalesce(v_Value,'')
|| ' from ' || coalesce(v_OldValue,'');

update OM_Incidents

set

Division = v_Division

where

IncidentId = v_IncidentId;

PERFORM om_ins_audit(v_IncidentId,'Modified',v_Message);

RETURN;

END; $BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

ALTER FUNCTION oms.om_change_i_division(integer, text, text) OWNER TO
oru;

From: Raghavendra [mailto:raghavendra(dot)rao(at)enterprisedb(dot)com]
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Dump functions alone

One more thing you can also get it from pg_get_functiondef() system
function.

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra
<raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:

You have two options.

* Use contrib module pg_extractor
https://github.com/omniti-labs/pg_extractor
* Use pg_proc catalog to get function definition

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/

On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP(at)oru(dot)com> wrote:

Hello all- Is there a way to just dump functions in a schema in to a
txt file/ sql file ? Thank you.

> Any help in getting function argument names is appreciated. Thank you

take a look at pg_catalog.pg_get_function_arguments(oid)

regards, jan