Re: checking if sequence exists

Поиск
Список
Период
Сортировка
От Bob Lunney
Тема Re: checking if sequence exists
Дата
Msg-id 1384565794.29182.YahooMailNeo@web122203.mail.ne1.yahoo.com
обсуждение исходный текст
Ответ на Re: checking if sequence exists  (Thara Vadakkeveedu <tharagv@yahoo.com>)
Список pgsql-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 just wrapping 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
].




В списке pgsql-admin по дате отправления:

Предыдущее
От: Della Rahawarin
Дата:
Сообщение: Error Installing PostgreSQL 9.3.1
Следующее
От: Kevin Grittner
Дата:
Сообщение: Re: checking if sequence exists