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
douglas.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com>
[cid:image001.jpg@01CD7A35.F48A4490] orbitz.com<http://www.orbitz.com/> |
ebookers.com<http://www.ebookers.com/> |
hotelclub.com<http://www.hotelclub.com/> |
cheaptickets.com<http://www.cheaptickets.com/> |
ratestogo.com<http://www.ratestogo.com/> |
asiahotels.com<http://www.asiahotels.com/>
<<inline: image001.jpg>>
