Re: Dump functions alone
От | Rajan, Pavithra |
---|---|
Тема | Re: Dump functions alone |
Дата | |
Msg-id | 924146AEF992184E84C7F5A5D6E2C87D0DB41397@EXCHMB05.conedison.net обсуждение исходный текст |
Ответ на | Re: Dump functions alone (Raghavendra <raghavendra.rao@enterprisedb.com>) |
Ответы |
Re: Dump functions alone
(Jan Otto <asche@me.com>)
|
Список | pgsql-general |
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.
On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <raghavendra.rao@enterprisedb.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
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.
В списке pgsql-general по дате отправления: