cast name to oid - Mailing list pgsql-general

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.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com