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@xxxxxxxxxxxxxxxx] 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@xxxxxxxxxxxxxxxx> wrote: You have two options.
On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra <RAJANP@xxxxxxx> wrote: Hello all- Is there a way to just dump functions in a schema in to a txt file/ sql file ? Thank you. |
