Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41883=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1ViQE4-0000Fl-Vi
 for pgsql-admin@arkaria.postgresql.org; Mon, 18 Nov 2013 14:54:57 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41883=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1ViQE4-0004Xw-01
 for pgsql-admin@arkaria.postgresql.org; Mon, 18 Nov 2013 14:54:56 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1ViQE1-0004UX-OJ
 for pgsql-admin@postgresql.org; Mon, 18 Nov 2013 14:54:53 +0000
Received: from mail-yh0-x22f.google.com ([2607:f8b0:4002:c01::22f])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1ViQDv-0003pZ-8m
 for pgsql-admin@postgresql.org; Mon, 18 Nov 2013 14:54:53 +0000
Received: by mail-yh0-f47.google.com with SMTP id 29so274696yhl.20
 for <pgsql-admin@postgresql.org>; Mon, 18 Nov 2013 06:54:46 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type;
 bh=X0ghpZioV3VcKYatVxV0xKh29pqzARkDKORaDy9FOac=;
 b=JryQRIbYDuIcgtqMekug16gdZsOk7S90ZSAMfGWAE+BLXJPu+eKFXETM6YJ9vXOz72
 +UXTPTz14d8EOPLZr6L2YXZpdTxUdSVfkYzSfJ7Tz3st/UTB+lii2Uq1tC0YD5NpXYHS
 3UZJC3O/DyjB+IynVsIx4T73sozPRXIrSRqYkCCY5eJHd1hV9mlpG0JbZkn11xtx7lll
 kJXzmgYZFdCYULo1IrC13eWpeoxk8vK/BmK9cj/+Hd6WK4Mx2x9znNwxwbm6B13qHnBX
 +PetXuIh1FAOGsxx7kEsOb6Upbculutkrtod1S/KWYSTQp6ZqUT1ctJl7zayD0+TpxYH
 O5Lg==
X-Received: by 10.236.128.71 with SMTP id e47mr197463yhi.186.1384786486256;
 Mon, 18 Nov 2013 06:54:46 -0800 (PST)
Received: from [192.168.204.25]
 (173-161-251-161-Philadelphia.hfc.comcastbusiness.net. [173.161.251.161])
 by mx.google.com with ESMTPSA id s21sm27750768yhk.9.2013.11.18.06.54.42
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 18 Nov 2013 06:54:45 -0800 (PST)
Message-ID: <528A2A31.6090205@gmail.com>
Date: Mon, 18 Nov 2013 09:54:41 -0500
From: Elliot <yields.falsehood@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: Thara Vadakkeveedu <tharagv@yahoo.com>,
 Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: checking if sequence exists
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>
In-Reply-To: <1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com>
Content-Type: multipart/alternative;
 boundary="------------050708040900010608040408"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------050708040900010608040408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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;






--------------050708040900010608040408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 2013-11-15 17:56, Thara Vadakkeveedu
      wrote:<br>
    </div>
    <blockquote
      cite="mid:1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com"
      type="cite">
      <div style="color:#000; background-color:#fff;
        font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial,
        Lucida Grande, sans-serif;font-size:14pt">
        <div style="RIGHT: auto"><span style="RIGHT: auto">By itself
            this sql works:</span></div>
        <div style="RIGHT: auto"><span style="RIGHT: auto">SELECT 0 FROM
            pg_class<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE relkind = 'S'<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND oid = ('public.' ||
            quote_ident('hibernate_sequence'))::regclass;</span></div>
        <div style="RIGHT: auto"><span style="RIGHT: auto"></span>&nbsp;</div>
        <div style="RIGHT: auto"><span style="RIGHT: auto">However when
            I create a function for it and run it I see an error</span></div>
        <div style="RIGHT: auto"><span style="RIGHT: auto"></span>&nbsp;</div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto">create function
            chk_sequence() returns integer as $$<br>
            BEGIN<br>
            IF EXISTS (SELECT 1 FROM pg_class<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE relkind = 'S'<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND oid = ('public.' ||
            quote_ident('hibernate_sequence')))::regclass</span></div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto">&nbsp; THEN<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return 1;<br>
            &nbsp; ELSE<br>
            &nbsp;return 0;<br>
            &nbsp;&nbsp;&nbsp; END IF;</span></div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto">END;<br>
            $$ language plpgsql;</span></div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto"></span>&nbsp;</div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto"></span>&nbsp;</div>
        <div style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal;
          FONT-FAMILY: HelveticaNeue, Helvetica Neue, Helvetica, Arial,
          Lucida Grande, sans-serif; COLOR: rgb(0,0,0); FONT-SIZE: 19px;
          RIGHT: auto"><span style="RIGHT: auto">select chk_sequence();<br>
            ERROR:&nbsp; operator does not exist: oid = text<br>
            LINE 3:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND oid = ('public.' ||
            quote_ident('hibernate_...<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ^<br>
            HINT:&nbsp; No operator matches the given name and argument
            type(s). You might need to add explicit type casts.<br>
            QUERY:&nbsp; SELECT EXISTS (SELECT 1 FROM pg_class<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE relkind = 'S'<br>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND oid = ('public.' ||
            quote_ident('hibernate_sequence')))::regclass</span></div>
        <div style="RIGHT: auto">&nbsp;</div>
        <div style="RIGHT: auto">Thanks.</div>
        <div style="FONT-FAMILY: HelveticaNeue, Helvetica Neue,
          Helvetica, Arial, Lucida Grande, sans-serif; FONT-SIZE: 14pt">
          <div style="FONT-FAMILY: HelveticaNeue, Helvetica Neue,
            Helvetica, Arial, Lucida Grande, sans-serif; FONT-SIZE:
            12pt"><br>
          </div>
        </div>
      </div>
    </blockquote>
    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.<br>
    <br>
    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:<br>
    <br>
    create function chk_sequence() returns integer as $$<br>
    BEGIN<br>
    IF EXISTS (SELECT 1<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM pg_catalog.pg_class c<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
    c.relnamespace<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE c.relkind IN ('S','s','')<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND n.nspname !~ '^pg_toast'<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND n.nspname = 'public'<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and c.relname = 'hibernate_sequence')<br>
    &nbsp; THEN<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return 1;<br>
    &nbsp; ELSE<br>
    &nbsp;return 0;<br>
    &nbsp;&nbsp;&nbsp; END IF;<br>
    END;<br>
    $$ language plpgsql;<br>
    <br>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

--------------050708040900010608040408--