Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Viblr-0000l1-Fi for pgsql-admin@arkaria.postgresql.org; Tue, 19 Nov 2013 03:14:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Viblp-0001LB-PV for pgsql-admin@arkaria.postgresql.org; Tue, 19 Nov 2013 03:14:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhUoY-0005ls-QD for pgsql-admin@postgresql.org; Sat, 16 Nov 2013 01:36:46 +0000 Received: from nm26-vm6.bullet.mail.ne1.yahoo.com ([98.138.91.119]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VhUoP-00075Y-Qf for pgsql-admin@postgresql.org; Sat, 16 Nov 2013 01:36:46 +0000 Received: from [98.138.100.112] by nm26.bullet.mail.ne1.yahoo.com with NNFMP; 16 Nov 2013 01:36:35 -0000 Received: from [98.138.89.198] by tm103.bullet.mail.ne1.yahoo.com with NNFMP; 16 Nov 2013 01:36:35 -0000 Received: from [127.0.0.1] by omp1056.mail.ne1.yahoo.com with NNFMP; 16 Nov 2013 01:36:35 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 555806.93687.bm@omp1056.mail.ne1.yahoo.com Received: (qmail 29228 invoked by uid 60001); 16 Nov 2013 01:36:35 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1384565795; bh=5QX1S3T9b/GFUzZfHB3AlTPDSOH3i1SzpbopRAFYxC4=; 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=0u6KE150VLZzkWmcN359P3aCHUaLSrFHFmlbJb7+T+34sCI0MAY2dS1OePerUHJQB37BOXEHIi9m+VUa+7mlT3Pxp+zFNcOfpdMAHXPY9evwFNjpMwYKOf3qRPdj63rBWqhqIf8sxdqKriSRGrKFYZ0e+5pfoAU4+UChGllMiYY= 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=AAyDfUkv0+2E8EnsOkGpyP5pLJx8wpmv5nJo+q7TFpReGVAREYU/88aiVOjhQO2h+NCy3zuWCFCBozFocO9BYvFDHWYhpl8Pgj5gSH2h2t+R9L3PZJV0Ur+SMnJTR3tIyG0xBNxqSw8ecgjM5rYuaPiUUaFGKfpENXCLyeMUsJA=; X-YMail-OSG: Keu1xykVM1lB5PhCA0glKY44hX_7XOMdbOhQ7Dd1mYu3ZyA jaK.4v5EI_J8q6V1SF4i4ZPXtGBzTGaYv38khCPMUJiQMfuAUCvIbmqYY9YK yjU8a7YyLW5GVRyuWsw22UOjLaDS_AGE6UUunBCw1DLcO9.tYr5JRc_kb2QW I.iVfMNr1WX9df2FTb1ZE2xHKb9q_rHbVRVbwLKi7_Fsj0MsW2Ii.B22EHdG DJNTTvffg_z28J3rv9pzwtAvUL.lAPTEqxtlmryvZhJd9q7hkBI5sdeC24HL ILcmWbfR0U1e0CYEWClgYUhUXZmJpO6NRF9Iz_6rRpDBPb0UcrjDbjDxkSCl y3.BXlNsaeZtqenENYBkKyltyuD3eTciyWNfF7376zgiNJxtWypNGw_QCrGY 2bD4pc7sQdxkxZbSfCuuEV7OYbthw7c5WU7l3TSPYgdvqhxQRR.fF9J0I2a8 ._1j0bqwAg_9RTdy3o3PCoNfiwfsY2K7BTkWPwFtLbhdBBWzPpBngtBQgZGA DReRA_EzFp3wZ1g2yk.J6A9xU5YvQ7Kg9FpDke4GYayBVyhKoJns7ffpYrWW R_zCGh_S3V0TzgnXyCf7fDsIaFoBZ66hpAGn4 Received: from [50.158.239.60] by web122203.mail.ne1.yahoo.com via HTTP; Fri, 15 Nov 2013 17:36:34 PST X-Rocket-MIMEInfo: 002.001, VGhhcmEsCgpIZXJlIGFyZSB5b3VyIGZ1bmN0aW9uczoKCmJvYj0.IGNyZWF0ZSBzZXF1ZW5jZSBmb287CkNSRUFURSBTRVFVRU5DRQpib2I9PiBjcmVhdGUgb3IgcmVwbGFjZSBmdW5jdGlvbiBwdWJsaWMuY2hrX3NlcXVlbmNlKG5hbWVzcGFjZSBuYW1lLCBzZXFfbmFtZSBuYW1lKQpib2ItPiDCoHJldHVybnMgaW50CmJvYi0.IMKgbGFuZ3VhZ2Ugc3FsCmJvYi0.IGFzICQkCmJvYiQ.IMKgIHNlbGVjdCBjb3VudCgqKTo6aW50IGFzIHNlcXVlbmNlX2V4aXN0cwpib2IkPiDCoCDCoCBmcm9tIHBnX2NsYXNzIGMBMAEBAQE- 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> <1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com> Message-ID: <1384565794.29182.YahooMailNeo@web122203.mail.ne1.yahoo.com> Date: Fri, 15 Nov 2013 17:36:34 -0800 (PST) From: Bob Lunney Reply-To: Bob Lunney Subject: Re: checking if sequence exists To: Thara Vadakkeveedu , Kevin Grittner , "pgsql-admin@postgresql.org" In-Reply-To: <1384556193.7870.YahooMailNeo@web125001.mail.ne1.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-103415633-1075179456-1384565794=:29182" X-Pg-Spam-Score: -2.0 (--) 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 ---103415633-1075179456-1384565794=:29182 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Thara,=0A=0AHere are your functions:=0A=0Abob=3D> create sequence foo;=0ACR= EATE SEQUENCE=0Abob=3D> create or replace function public.chk_sequence(name= space name, seq_name name)=0Abob-> =A0returns int=0Abob-> =A0language sql= =0Abob-> as $$=0Abob$> =A0 select count(*)::int as sequence_exists=0Abob$> = =A0 =A0 from pg_class c, pg_namespace n=0Abob$> =A0 =A0where c.relnamespace= =3D n.oid=0Abob$> =A0 =A0 =A0and c.relkind in ('S', 's', '')=0Abob$> =A0 = =A0 =A0and pg_table_is_visible(c.oid)=0Abob$> =A0 =A0 =A0and n.nspname =3D = $1=0Abob$> =A0 =A0 =A0and c.relname =3D $2;=0Abob$> $$;=0ACREATE FUNCTION= =0Abob=3D> select chk_sequence('public', 'foo');=0A=A0chk_sequence=0A------= --------=0A=A0 =A0 =A0 =A0 =A0 =A0 1=0A(1 row)=0A=0Abob=3D> select chk_sequ= ence('public', 'bar');=0A=A0chk_sequence=0A--------------=0A=A0 =A0 =A0 =A0= =A0 =A0 0=0A(1 row)=0A=0Abob=3D> ----=0Abob=3D> -- when you get tired of t= yping 'public' use this version=0Abob=3D> ----=0Abob=3D> create or replace = function chk_sequence(seq_name name)=0Abob-> =A0 returns int=0Abob-> =A0 la= nguage sql=0Abob-> as $$=0Abob$> =A0 select chk_sequence('public', $1);=0Ab= ob$> $$;=0ACREATE FUNCTION=0Abob=3D> select chk_sequence('foo');=0A=A0chk_s= equence=0A--------------=0A=A0 =A0 =A0 =A0 =A0 =A0 1=0A(1 row)=0A=0Abob=3D>= select chk_sequence('bar');=0A=A0chk_sequence=0A--------------=0A=A0 =A0 = =A0 =A0 =A0 =A0 0=0A(1 row)=0A=0A=0A=0A=0AOn Friday, November 15, 2013 5:39= PM, Thara Vadakkeveedu wrote:=0A =0ABy itself this sql= works:=0ASELECT 0 FROM pg_class=0A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHE= RE 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'))::regclass;=0A=A0=0AHoweve= r 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_ide= nt('hibernate_sequence')))::regclass=0A=A0 THEN=0A=A0=A0=A0=A0=A0=A0=A0 ret= urn 1;=0A=A0 ELSE=0A=A0return 0;=0A=A0=A0=A0 END IF;=0AEND;=0A$$ language p= lpgsql;=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 oi= d =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 N= o operator matches the given name and argument type(s). You might need to a= dd 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=0A ('public.' || quote_ident('hiber= nate_sequence')))::regclass=0A=A0=0AThanks.=0AFrom: Elliot =0ATo: Thara Vadakkeveedu ; Kevin Grittner <= kgrittn@ymail.com>; "pgsql-admin@postgresql.org" =0ASent: Friday, November 15, 2013 4:13 PM=0ASubject: Re: [ADMIN] checki= ng if sequence exists=0A=0A=0A=0AOn 2013-11-15 16:09, Thara Vadakkeveedu wr= ote:=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 st= ructures like if statements don't exist in straight sql - you need a proced= ural 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 yo= u can use a "DO" block, which is sometimes what I use for deploy scripts [h= ttp://www.postgresql.org/docs/9.3/static/sql-do.html =0A]. ---103415633-1075179456-1384565794=:29182 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Thara,

Here are your functions:
<= span>
bob=3D&= gt; create sequence foo;
CR= EATE SEQUENCE
bob=3D>= create or replace function public.chk_sequence(namespace name, seq_name na= me)
bob->  retur= ns int
bob->  la= nguage sql
bob-> as $= $
bob$>   select= count(*)::int as sequence_exists
bob$>     from pg_class c, pg_namespace n
bob$>    where c.reln= amespace =3D n.oid
bob$&= gt;      and c.relkind in ('S', 's', '')
bob$>      and pg_table_is_visible(c.oid)
bob$>      and n.nspname =3D $1
bob$>      and c.relname = =3D $2;
bob$> $$;
CREATE FUNCTION
bob=3D> select chk_sequence('pub= lic', 'foo');
 chk_= sequence
--------------<= /div>
      &nb= sp;     1
(1 r= ow)

bob=3D> select chk_sequence('public'= , 'bar');
 chk_sequ= ence
--------------
            = 0
(1 row)

bob=3D> ----
bob=3D> -- when you get tired of typing 'public' use this vers= ion
bob=3D> ----
bob=3D> 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= =3D> select chk_sequence('foo');
 chk_sequence
--------------
&n= bsp;           1
(1 row)
bob=3D> select= chk_sequence('bar');
&n= bsp;chk_sequence
-------= -------
    &n= bsp;       0
(1 row)



On Friday, November 15, 2013 5:39 PM, Thara Vadakkeveedu <= tharagv@yahoo.com> wrote:
By itself this sql works:
=0A
SELECT 0 FROM pg_class
 &= nbsp;           WHERE rel= kind =3D 'S'
       &n= bsp;       AND oid =3D ('public.' || quote_id= ent('hibernate_sequence'))::regclass;
=0A
 
=0A
However w= hen I create a function for it and run it I see an error
=0A 
=0A
create function chk_sequence() ret= urns integer as $$
BEGIN
IF EXISTS (SEL= ECT 1 FROM pg_class
      &= nbsp;      WHERE relkind =3D 'S'
             A= ND oid =3D ('public.' || quote_ident('hibernate_sequence')))::regclass
=0A
=   THEN
        re= turn 1;
  ELSE
 return 0;
    END IF;
=0A
END;
$$ lan= guage plpgsql;
=0A
 
=0A
 
=0A
select chk_sequence();
ERROR:  operator does not exist: oid =3D text
LINE 3:           &= nbsp;  AND oid =3D ('public.' || quote_ident('hibernate_...
           = ;            &n= bsp;     ^
HINT:  No operator ma= tches the given name and argument type(s). You might need to add explicit t= ype casts.
QUERY:  SELECT EXISTS (SELECT 1 FROM pg_c= lass
        &nbs= p;    WHERE relkind =3D 'S'
          = ;   AND oid =3D=0A ('public.' || quote_ident('hibernate_sequence'= )))::regclass
=0A
 
=0A
Thanks.
=0A
=0A
=0A
=0A
From: Elliot <yields.falsehood@gmail.com><= br clear=3D"none">To: Thara= Vadakkeveedu <tharagv@yahoo.com>; Kevin Grittner <kgrittn@ymail.c= om>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> Sent: Frida= y, November 15, 2013 4:13 PM
Subject: Re: [ADMIN] checking if sequence exists
=0A

=0A
=0A
=0A
=0A
On 2013= -11-15 16:09, Thara Vadakkeveedu wrote:
=0A=0A
=0A=0A
=0A"First, is this code in a plpgsql contex"?
=0A
 
=0A
No, that is my problem.
=0A
 
=0A
Does it h= ave to be inside a Create function block or can just wrapping = the if  with a BEGIN END; suffice ?
=0A
 

Control structures like if statements don't exist in = straight sql - you need a procedural language like pl/pgsql for that. Wrapp= ing sql statements in begin/end only affects the transactional context of t= he statements, it does not cause them to be interpreted as pl/pgsql. You ca= n either create a function or you can use a "DO" block, which is sometimes = what I use for deploy scripts [htt= p://www.postgresql.org/docs/9.3/static/sql-do.html =0A
].




=
---103415633-1075179456-1384565794=:29182--