Thread: cast name to oid
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
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com