Re: checking if sequence exists

[Thara Vadakkeveedu <tharagv@xxxxxxxxx>]

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@xxxxxxxxx>
To: Thara Vadakkeveedu <tharagv@xxxxxxxxx>; Kevin Grittner <kgrittn@xxxxxxxxx>; "pgsql-admin@xxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxx>
Sent: Friday, November 15, 2013 4:13 PM
Subject: Re: 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

].