Monday, September 22, 2008

PostgreSql Oid to Name (oid2name)

Function gets name of object by oid and its catalog name:
CREATE OR REPLACE FUNCTION oid2name(
  id integer,
  catalog varchar
)
RETURNS varchar AS
$$
SELECT cast(c.name as varchar)
FROM 
  (
  select
    CASE $2
    WHEN 'pg_class' THEN
      (select t.relname as name
       from pg_catalog.pg_class t
       WHERE oid = $1)
    WHEN 'pg_constraint' THEN
       (select t.conname as name
        from pg_catalog.pg_constraint t
        WHERE oid = $1)
    WHEN 'pg_type' THEN
       (select t.typname as name
        from pg_catalog.pg_type t
        WHERE oid = $1)
    WHEN 'pg_conversion' THEN
       (select t.conname as name
        from pg_catalog.pg_conversion t
        WHERE oid = $1) 
    WHEN 'pg_proc' THEN
       (select t.proname as name
        from pg_catalog.pg_proc t
        WHERE oid = $1) 
    WHEN 'pg_rewrite' THEN
       (select t.rulename as name
        from pg_catalog.pg_rewrite t
        WHERE oid = $1)
    WHEN 'pg_trigger' THEN
       (select t.tgname as name
        from pg_catalog.pg_trigger t
        WHERE oid = $1)
    WHEN 'pg_language' THEN
      (select t.lanname as name
       from pg_catalog.pg_language t
       WHERE oid = $1)
    WHEN 'pg_namespace' THEN
      (select t.nspname as name
       from pg_catalog.pg_namespace t  
       WHERE oid = $1)
    END as name
  ) as c
$$
LANGUAGE 'sql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER;

No comments: