Showing posts with label name by oid. Show all posts
Showing posts with label name by oid. Show all posts

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;