I got my function dump function to work. Enhancing to handle errors if the object doesn’t exist. I want to add an exception block, to trap the object not found error. But when I changed the input parameter type from regproc to text, I was no longer getting matches. I am trying to explicitly cast the object name as an oid. Can someone let me know the correct way to do this? This is failing where p.oid = cast(proname as regproc); NOTICE: found dba_work.pg_get_functiondef2 WARNING: sqlstate 42846 WARNING: sqlerrm cannot cast type text to regproc Thanks Current content CREATE OR REPLACE FUNCTION dba_work.pg_get_functiondef2(proname text) RETURNS text AS $BODY1$ declare xsource text; begin if public.ifexists(proname) then raise notice 'found %', proname; begin select into xsource E'\n' ||'CREATE OR REPLACE FUNCTION '||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||')' || E'\nRETURNS '||t.typname||' AS' || E'\n$BODY$\n' || prosrc || E'\n$BODY$\n' ||' LANGUAGE ''' || l.lanname || E''' VOLATILE;' || E'\n alter function '||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') owner to '||pg_get_userbyid(p.proowner)||';' || regexp_replace(replace(E'\n GRANT EXECUTE ON FUNCTION '||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') TO ' || array_to_string(proacl,E'\n GRANT EXECUTE ON FUNCTION '||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') TO ') ,' =X',' public=X') ,E'=(.*?)(?:\s|$|\n)',E';\n','g') from pg_proc p inner join pg_type t on p.prorettype = t.oid inner join pg_namespace n on p.pronamespace = n.oid inner join pg_language l on p.prolang = l.oid where p.oid = cast(proname as name); Exception when others Then xsource = 'Object:'||proname||' not found'; raise warning 'sqlstate %', SQLSTATE; raise warning 'sqlerrm %', SQLERRM; end; end if; return xsource; end; $BODY1$ LANGUAGE plpgsql STABLE; ALTER FUNCTION dba_work.pg_get_functiondef2(text) OWNER TO dlittle; Doug Little Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 |
