Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhSJc-0002xI-Pc for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 22:56:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VhSJc-00015B-7I for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 22:56:40 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhSJa-000154-W3 for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 22:56:39 +0000 Received: from nm15-vm6.bullet.mail.ne1.yahoo.com ([98.138.91.108]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhSJX-0000Uk-L2 for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 22:56:38 +0000 Received: from [98.138.100.116] by nm15.bullet.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 22:56:33 -0000 Received: from [98.138.101.176] by tm107.bullet.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 22:56:33 -0000 Received: from [127.0.0.1] by omp1087.mail.ne1.yahoo.com with NNFMP; 15 Nov 2013 22:56:33 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 740621.87728.bm@omp1087.mail.ne1.yahoo.com Received: (qmail 10453 invoked by uid 60001); 15 Nov 2013 22:56:33 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1384556193; bh=QAnBctzhuie9jMkUZbn4wVPVkAK4sOksN7/CCSTPF34=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=0xWLIci4X46U+FMbbrk04tfkTp5Ls5sExHcWq5XRjzVQ0SnnkXx+N669R1deXn8tdIIbfTCiUgxSqoJnFJ8FAh4ded9g+giCCnet5yN6sz7Y6ICP2XCUvukB34Mpfw1UMbEQrHCWS9qlLsfx38CEIFLamIpYf7WgG2k4E1rF2aE= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=Q0cPLjmkY0NZz5mDXenJRN3oQxtr+Jm2lIMUuUnGe9bTDOn/ItjeutPgjxO8rGrz61KmMJ7iU+cccI9XilLSS4YslV0IQzeGDgQJYx8nVJe5fW18QSb7Z8PP9+pZUGzoBz6AvcSEh0AvmMuZxF5AuTLTSHVJg/8luVRqiiOUc2E=; X-YMail-OSG: lEEtsiAVM1m5gOBjzyTNb4wwjczh7YP2idiZlp4MF6S4jUM oQT2dordacV.tcZcocOIncVqJFstedxFbfM.yGltUqxPe4DlrNu8PbrtfDE. mrro4vXK85L6KCH0D2dv6WXv48rrYcluA6DuAL.lRhhCGjBBy5e_iZNAMFug UkPTLVzlMqeLdcEzNGnP.zw.Na_g.amnHGawyUJVGUUiRkvl9iI3mOYlrf9P _69TWe4PT0Gjw1VJr6C28VpdOiw5YMlkhkNo73tBKn0_GMtpmGSsuM57T0Z2 2YU0BfA99ifl1OJ8ayrHhXf9DksjjkK1O5bHi8fdx9GobPadoJ25gY99K1Va lHMzhBxKZB.KptCSLybZvK_LQ.7czPFIO9wJoZURoOhvf5CEWLubNMYLZHhv QJgLefpzalUaW.8iGox7RBrr3254y7HwIuZoJkHHwSR_LKU7lcrjy4zus5AT DjoK0.ghwnV7F7ZO7gbdAMxB7jJ7WAoc5U4pIX8KHxAuLdYg9KoWahfYIAwU Yic4tTX.n6.rY4WdCv5gy38vY5KcA7pl7tanwv8dFxjGTQiziVKjS0oBX22c 4MaTl53TbN1Bn4I5L8SlYeeGGpsOpJc0- Received: from [50.241.80.25] by web125001.mail.ne1.yahoo.com via HTTP; Fri, 15 Nov 2013 14:56:33 PST X-Rocket-MIMEInfo: 002.001, QnkgaXRzZWxmIHRoaXMgc3FsIHdvcmtzOgpTRUxFQ1QgMCBGUk9NIHBnX2NsYXNzCsKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoCBXSEVSRSByZWxraW5kID0gJ1MnCsKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqAgQU5EIG9pZCA9ICgncHVibGljLicgfHwgcXVvdGVfaWRlbnQoJ2hpYmVybmF0ZV9zZXF1ZW5jZScpKTo6cmVnY2xhc3M7CsKgCkhvd2V2ZXIgd2hlbiBJIGNyZWF0ZSBhIGZ1bmN0aW9uIGZvciBpdCBhbmQgcnVuIGl0IEkgc2VlIGFuIGVycm9yCsKgCmNyZWF0ZSBmdW5jdGlvbiBjaGtfc2VxdWUBMAEBAQE- X-Mailer: YahooMailWebService/0.8.163.597 References: <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> Message-ID: <1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com> Date: Fri, 15 Nov 2013 14:56:33 -0800 (PST) From: Thara Vadakkeveedu Reply-To: Thara Vadakkeveedu Subject: Re: checking if sequence exists To: Kevin Grittner , "pgsql-admin@postgresql.org" In-Reply-To: <52868E61.1000806@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1184048422-1593953828-1384556193=:7870" X-Pg-Spam-Score: -0.6 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --1184048422-1593953828-1384556193=:7870 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable By itself this sql works:=0ASELECT 0 FROM pg_class=0A=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 WHERE relkind =3D 'S'=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 AND oid =3D ('public.' || quote_ident('hibernate_sequence'))::reg= class;=0A=A0=0AHowever when I create a function for it and run it I see an = error=0A=A0=0Acreate function chk_sequence() returns integer as $$=0ABEGIN= =0AIF EXISTS (SELECT 1 FROM pg_class=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= WHERE relkind =3D 'S'=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND oid =3D (= 'public.' || quote_ident('hibernate_sequence')))::regclass=0A=A0 THEN=0A=A0= =A0=A0=A0=A0=A0=A0 return 1;=0A=A0 ELSE=0A=A0return 0;=0A=A0=A0=A0 END IF;= =0AEND;=0A$$ language plpgsql;=0A=A0=0A=A0=0Aselect chk_sequence();=0AERROR= :=A0 operator does not exist: oid =3D text=0ALINE 3:=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 AND oid =3D ('public.' || quote_ident('hibernate_...=0A= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 ^=0AHINT:=A0 No operator matches the given name and argument type= (s). You might need to add explicit type casts.=0AQUERY:=A0 SELECT EXISTS (= SELECT 1 FROM pg_class=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE relkind= =3D 'S'=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 AND oid =3D ('public.' || q= uote_ident('hibernate_sequence')))::regclass=0A=A0=0AThanks.=0AFrom: Elliot= =0ATo: Thara Vadakkeveedu ;= Kevin Grittner ; "pgsql-admin@postgresql.org" =0ASent: Friday, November 15, 2013 4:13 PM=0ASubject: R= e: [ADMIN] checking if sequence exists=0A=0A=0A=0AOn 2013-11-15 16:09, Thar= a Vadakkeveedu wrote:=0A=0A"First, is this code in a plpgsql contex"?=0A>= =A0=0A>No, that is my problem.=0A>=A0=0A>Does it have to be inside a Create= function block or can justwrapping the if=A0 with a BEGIN END; suffice ?= =0A>=A0=0A>Control structures like if statements don't exist in straight sq= l - you need a procedural language like pl/pgsql for that. Wrapping sql sta= tements in begin/end only affects the transactional context of the statemen= ts, it does not cause them to be interpreted as pl/pgsql. You can either cr= eate 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 = =0A]. --1184048422-1593953828-1384556193=:7870 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
By its= elf this sql works:
SELECT 0 FROM pg_cla= ss
           &nb= sp; WHERE relkind =3D 'S'
       &nbs= p;       AND oid =3D ('public.' || quote_iden= t('hibernate_sequence'))::regclass;
 
However when I creat= e a function for it and run it I see an error
 
create function chk_sequence() returns integer as $$
BEGIN
IF E= XISTS (SELECT 1 FROM pg_class
       =       WHERE relkind =3D 'S'
   &= nbsp;         AND oid =3D ('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 = =3D text
LINE 3:         &n= bsp;    AND oid =3D ('public.' || quote_ident('hibernate_...=
            = ;            &n= bsp;    ^
HINT:  No operator matches the given name = and argument type(s). You might need to add explicit type casts.
QUERY:&= nbsp; SELECT EXISTS (SELECT 1 FROM pg_class
    &nbs= p;        WHERE relkind =3D 'S'
 = ;            AND oid= =3D ('public.' || quote_ident('hibernate_sequence')))::regclass
 
Thanks.
From: Elliot <yields.falsehood@gmail.com>
= To: Thara Vadakkeveedu <tha= ragv@yahoo.com>; Kevin Grittner <kgrittn@ymail.com>; "pgsql-admin@= postgresql.org" <pgsql-admin@postgresql.org>
Sent: Friday, November 15, 2013 4:13 PM
<= SPAN style=3D"FONT-WEIGHT: bold">Subject: Re: [ADMIN] checking i= f sequence exists

On 2013-11-15 16:09, Thara Vadakkeveedu wrote:
<= /DIV>
"First, is this code in a plpgsql contex"?
 
No, that is my p= roblem.
 
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 exis= t 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. Y= ou can either create a function or you can use a "DO" block, which is somet= imes what I use for deploy scripts [http= ://www.postgresql.org/docs/9.3/static/sql-do.html=20
].


=
--1184048422-1593953828-1384556193=:7870--