From: Bob Lunney <bob_lunney@yahoo.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: Fri, 15 Nov 2013 17:36:34 -0800 (PST)
Message-ID: <1384565794.29182.YahooMailNeo@web122203.mail.ne1.yahoo.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>
Thara,
Here are your functions:
bob=> create sequence foo;
CREATE SEQUENCE
bob=> create or replace function public.chk_sequence(namespace name, seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select count(*)::int as sequence_exists
bob$> from pg_class c, pg_namespace n
bob$> where c.relnamespace = n.oid
bob$> and c.relkind in ('S', 's', '')
bob$> and pg_table_is_visible(c.oid)
bob$> and n.nspname = $1
bob$> and c.relname = $2;
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('public', 'foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('public', 'bar');
chk_sequence
--------------
0
(1 row)
bob=> ----
bob=> -- when you get tired of typing 'public' use this version
bob=> ----
bob=> create or replace function chk_sequence(seq_name name)
bob-> returns int
bob-> language sql
bob-> as $$
bob$> select chk_sequence('public', $1);
bob$> $$;
CREATE FUNCTION
bob=> select chk_sequence('foo');
chk_sequence
--------------
1
(1 row)
bob=> select chk_sequence('bar');
chk_sequence
--------------
0
(1 row)
On Friday, November 15, 2013 5:39 PM, Thara Vadakkeveedu <tharagv@yahoo.com> 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.
From: Elliot <yields.falsehood@gmail.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.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].
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: bob_lunney@yahoo.com, tharagv@yahoo.com, kgrittn@ymail.com
Subject: Re: checking if sequence exists
In-Reply-To: <1384565794.29182.YahooMailNeo@web122203.mail.ne1.yahoo.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