Re: checking if sequence exists

[Elliot <yields.falsehood@xxxxxxxxx>]

On 2013-11-15 17:56, Thara Vadakkeveedu wrote:

By itself this sql works:

SELECT 0 FROM pg_class
             WHERE relkind = 'S'
               AND oid = ('public.' || quote_ident('hibernate_sequence'))::regclass;

 

However when I create a function for it and run it I see an error

 

create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_class
             WHERE relkind = 'S'
             AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass

  THEN
        return 1;
  ELSE
 return 0;
    END IF;

END;
$$ language plpgsql;

 

 

select chk_sequence();
ERROR:  operator does not exist: oid = text
LINE 3:              AND oid = ('public.' || quote_ident('hibernate_...
                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT EXISTS (SELECT 1 FROM pg_class
             WHERE relkind = 'S'
             AND oid = ('public.' || quote_ident('hibernate_sequence')))::regclass

 

Thanks.

You've got two different queries there. In the first example you're casting the string public.hibernate_sequence to regclass. In the second you've got the parentheses capturing the regclass cast around the entire query.

However, you probably don't want to use this query anyway as the regclass cast will fail with an exception if the sequence doesn't exist (meaning you'd have to wrap it in an exception catching block instead of an in-else block). This uses the query I sent out in an early response:

create function chk_sequence() returns integer as $$
BEGIN
IF EXISTS (SELECT 1
           FROM pg_catalog.pg_class c
                  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
           WHERE c.relkind IN ('S','s','')
             AND n.nspname !~ '^pg_toast'
             AND n.nspname = 'public'
             and c.relname = 'hibernate_sequence')
  THEN
        return 1;
  ELSE
 return 0;
    END IF;
END;
$$ language plpgsql;