Re: checking if sequence exists

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;