From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
To: Kevin Grittner <kgrittn@ymail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: checking if sequence exists
Date: Mon, 18 Nov 2013 09:54:41 -0500
Message-ID: <528A2A31.6090205@gmail.com> (raw)
In-Reply-To: <1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com>
References: <CA+yMuNq+unyKgCrpfmmb2WK0=toMzqrx7491n=upM8Rrj3KtwQ@mail.gmail.com>
<CAKoxK+5DvJYF3tor6=kJKdfhpxoui6PAFjuhy9WzHjZbngGDPQ@mail.gmail.com>
<CA+yMuNqxZu+xZeGiD-fdC+QZK-x9+zg03X8xzDpBvQcVTncYxg@mail.gmail.com>
<1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com>
<1384544163.79352.YahooMailNeo@web125002.mail.ne1.yahoo.com>
<1384545985.96990.YahooMailNeo@web162906.mail.bf1.yahoo.com>
<1384549763.68859.YahooMailNeo@web125005.mail.ne1.yahoo.com>
<52868E61.1000806@gmail.com>
<1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: yields.falsehood@gmail.com, tharagv@yahoo.com, kgrittn@ymail.com
Subject: Re: checking if sequence exists
In-Reply-To: <528A2A31.6090205@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox