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;