Thread: cast name to oid

cast name to oid

From
"Little, Douglas"
Date:

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

 Description: cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Attachment

Re: cast name to oid

From
Sergey Konoplev
Date:
Hi,

On Wed, Aug 15, 2012 at 1:02 AM, Little, Douglas
<DOUGLAS.LITTLE@orbitz.com> wrote:
> 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
>
> CREATE OR REPLACE FUNCTION dba_work.pg_get_functiondef2(proname text)
> ...
>    where p.oid = cast(proname as name);
> ...

Typo?

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204