From: | Thara Vadakkeveedu <tharagv(at)yahoo(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: checking if sequence exists |
Date: | 2013-11-15 22:56:33 |
Message-ID: | 1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From: Elliot <yields(dot)falsehood(at)gmail(dot)com>
To: Thara Vadakkeveedu <tharagv(at)yahoo(dot)com>; Kevin Grittner <kgrittn(at)ymail(dot)com>; "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
"First, is this code in a plpgsql contex"?
>
>No, that is my problem.
>
>Does it have to be inside a Create function block or can justwrapping the if with a BEGIN END; suffice ?
>
>Control structures like if statements don't exist in straight sql - you need a procedural language like pl/pgsql for that. Wrapping sql statements in begin/end only affects the transactional context of the statements, it does not cause them to be interpreted as pl/pgsql. You can either create a function or you can use a "DO" block, which is sometimes what I use for deploy scripts [http://www.postgresql.org/docs/9.3/static/sql-do.html
].
From | Date | Subject | |
---|---|---|---|
Next Message | Bob Lunney | 2013-11-16 01:36:34 | Re: checking if sequence exists |
Previous Message | Elliot | 2013-11-15 21:13:05 | Re: checking if sequence exists |