Thread: Dump functions alone

Dump functions alone

From
"Rajan, Pavithra"
Date:

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

Re: Dump functions alone

From
Raghavendra
Date:
You have two options.

---
Regards,
Raghavendra
EnterpriseDB Corporation



On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP@oru.com> wrote:

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


Re: Dump functions alone

From
Raghavendra
Date:
One more thing you can also get it from pg_get_functiondef() system function.

---
Regards,
Raghavendra
EnterpriseDB Corporation



On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
You have two options.

---
Regards,
Raghavendra
EnterpriseDB Corporation



On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP@oru.com> wrote:

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



Re: Dump functions alone

From
"Rajan, Pavithra"
Date:

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.rao@enterprisedb.com]
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general@postgresql.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



On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:

You have two options.

 

---

Regards,

Raghavendra

EnterpriseDB Corporation



On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP@oru.com> wrote:

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

 

 

Re: Dump functions alone

From
"Rajan, Pavithra"
Date:

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.proargtypes)||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.rao@enterprisedb.com]
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general@postgresql.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



On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:

You have two options.

 

---

Regards,

Raghavendra

EnterpriseDB Corporation



On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP@oru.com> wrote:

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

 

 

Re: Dump functions alone

From
Jan Otto
Date:
> Any help in getting function argument names is appreciated.  Thank you

take a look at pg_catalog.pg_get_function_arguments(oid)

regards, jan