Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YxUl7-0001MD-EQ for pgsql-sql@arkaria.postgresql.org; Wed, 27 May 2015 06:24:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YxUl6-0003El-BM for pgsql-sql@arkaria.postgresql.org; Wed, 27 May 2015 06:24:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YxUl5-0003Eb-2h for pgsql-sql@postgresql.org; Wed, 27 May 2015 06:24:07 +0000 Received: from mail-wi0-x22e.google.com ([2a00:1450:400c:c05::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1YxUl1-0000I2-3M for pgsql-sql@postgresql.org; Wed, 27 May 2015 06:24:06 +0000 Received: by wizo1 with SMTP id o1so9045148wiz.1 for ; Tue, 26 May 2015 23:24:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=references:mime-version:in-reply-to:content-type :content-transfer-encoding:message-id:cc:from:subject:date:to; bh=7DcWVQrtPdBSvYLVrBfRsz5xFEvbPKFT2HWpfMd5stc=; b=dYy6t360b4xIqOGFe7j6uYmmlsRG1GauQ9wZJoTXv+/4lhRgjxj/Wb3jBwceR7KeYi EbhnihgWiYgL/rocJOdI7jOeVgA1cc7hAwUcCPLNtI154QkIMgQO/Zuohy/iwdJ8cYZb Sf946u/J3ZLbtj8OZtHYWeNa788Qn9aWcCjgXOHYOyg2YIJ1RJ9aNqHDp+3vjqk1xAF4 85kqd56zJNW8hH98XuY/tBQu2BaJVItsEl3a7pAF7XSu3lGnteq3fZHvyZ2LiTf6i6Xc g739kgMH814aAbxWyCKMIkOy3fjPq944E+HnPA1vZV5ZqMA/J06NyaOs6AhdteGLROD9 mkIg== X-Received: by 10.180.106.6 with SMTP id gq6mr2696212wib.39.1432707841240; Tue, 26 May 2015 23:24:01 -0700 (PDT) Received: from [192.168.71.122] (77-234-95-169.pool.digikabel.hu. [77.234.95.169]) by mx.google.com with ESMTPSA id ng5sm1897493wic.24.2015.05.26.23.24.00 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 26 May 2015 23:24:00 -0700 (PDT) References: Mime-Version: 1.0 (1.0) In-Reply-To: Content-Type: multipart/alternative; boundary=Apple-Mail-DFD02705-C6B1-445D-91C7-CB17C2D0A10E Content-Transfer-Encoding: 7bit Message-Id: <355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com> Cc: "pgsql-sql@postgresql.org" X-Mailer: iPad Mail (12F69) From: daku.sandor@gmail.com Subject: Re: Postgres CTE issues Date: Wed, 27 May 2015 08:24:00 +0200 To: Shekar Tippur X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail-DFD02705-C6B1-445D-91C7-CB17C2D0A10E Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable =20 Hi, Remove the the single quotes around NEW.var1 and you'll be golden. Regards, Sandor Daku > On 26 May 2015, at 19:07, Shekar Tippur wrote: >=20 > Here is a small snippet on how I got to the error. I am creating a trigger= function that returns a trigger. > As you can see, I get a error at the end. Appreciate any help in this rega= rd. >=20 > -- Create table A >=20 > create table A ( >=20 > var1 varchar(40), >=20 > var2 varchar(40) ); >=20 >=20 >=20 > -- Create table B >=20 > create table B ( >=20 > "id" SERIAL PRIMARY KEY, >=20 > name varchar(40)); >=20 >=20 > -- Create table C >=20 > create table C ( >=20 > "id" SERIAL PRIMARY KEY, >=20 > name varchar(40) >=20 > , b_id integer references B(id) NOT NULL); >=20 >=20 > -- Create a trigger function >=20 > CREATE OR REPLACE FUNCTION fn_test() RETURNS trigger AS $BODY$ >=20 > DECLARE >=20 > a_id int; >=20 > b_id int; >=20 > c_id int; >=20 > BEGIN >=20 > INSERT INTO B (name) VALUES (NEW.var1); >=20 > b_id :=3D (select id from B where name =3D 'NEW.var1'); >=20 > INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id); >=20 >=20 > return NEW; >=20 > END; >=20 > $BODY$ LANGUAGE plpgsql; >=20 > -- Create trigger >=20 > CREATE TRIGGER tr_test >=20 > BEFORE insert or UPDATE >=20 > ON A >=20 > FOR EACH ROW >=20 >=20 > EXECUTE PROCEDURE fn_test(); >=20 >=20 >=20 > insert into A (var1, var2) values ('Hello', 'World'); >=20 > ERROR: null value in column "b_id" violates not-null constraint >=20 > DETAIL: Failing row contains (1, World, null). >=20 > CONTEXT: SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id= )" >=20 > PL/pgSQL function fn_test() line 17 at SQL statement >=20 >=20 >> On Tue, May 26, 2015 at 9:14 AM, David G. Johnston wrote: >> re-including the list >>=20 >>> On Tue, May 26, 2015 at 9:09 AM, Shekar Tippur wrote= : >>=20 >>> =E2=80=8B >>>> On Tue, May 26, 2015 at 9:00 AM, David G. Johnston wrote: >>>>> On Tue, May 26, 2015 at 8:45 AM, Shekar Tippur wro= te: >>>>=20 >>>>>=20 >>>>> This is what I am trying: >>>>>=20 >>>>> WITH x AS=20 >>>>>=20 >>>>> (INSERT INTO industry (name,abbr,description,cr_date,last_upd)=20 >>>>>=20 >>>>> VALUES ('df','','',now(),now()) returning id) insert into sector (name= ,description,cr_date,last_upd,industry_id) select 's1','',now(),now(),id fro= m x; >>>>>=20 >>>>> I get a error: >>>>>=20 >>>>> ERROR: insert or update on table "sector" violates foreign key constr= aint "sector_id_fkey" >>>>>=20 >>>>> DETAIL: Key (id)=3D(394) is not present in table "industry". >>>>>=20 >>>>> If I execute the insert individually, I am able to insert a record. Wo= nder what I am doing wrong. >>>>>=20 >>>>> I have been stuck with this issue for over 24 hours. Appreciate any he= lp. >>>>=20 >>>> It is not possible to accomplish your goal using a CTE. =46rom the poi= nt of view of both tables the data they can see is what was present before t= he statement began. >>>>=20 >>>> The more usual way to accomplish this is the write a pl/pgsql function w= ith two statements and passing the ID between them using an intermediate var= iable. >>>>=20 >>>> =E2=80=8BDavid J. >> =E2=80=8B>>>>>>>>>>>>>>>>>>>=E2=80=8B=20 >> =E2=80=8B=E2=80=8BI have tried that as well. >>=20 >> INSERT INTO industry (name,abbr,description,cr_date,last_= upd) VALUES (NEW.industry,'','',now(),now()) returning id into industry_id; >>=20 >> industry_id :=3D (select industry_id from industry where n= ame =3D 'NEW.industry'); >>=20 >> raise notice 'industry id is %', industry_id;=20 >>=20 >> INSERT INTO sector (name,description,cr_date,last_upd,ind= ustry_id) VALUES (NEW.sector,'',now(),now(),industry_id) returning id into s= ector_id; >>=20 >> -- I get a new industry ID but a new row is not inserted. I am guessing t= his is the case because it takes all the transactions as atomic. As a result= , I get a foreign key violation. >>=20 >>=20 >> =E2=80=8B>>>>>>>>>>>>>>>>>>=E2=80=8B >>=20 >> =E2=80=8BIf you are using a trigger you should also provide the relevant C= REATE TRIGGER statement... >>=20 >> In fact, you really you supply a self-contained example. >>=20 >> Also, please do not top-post. >>=20 >> David J. >=20 --Apple-Mail-DFD02705-C6B1-445D-91C7-CB17C2D0A10E Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
 
Hi,

Remove the the single quotes around NEW.var1 and you'll be golden.
Regards,
Sandor Daku


On 26 M= ay 2015, at 19:07, Shekar Tippur <ct= ippur@gmail.com> wrote:

<= div dir=3D"ltr">
Here is a small snippet on how I got to the error. I am= creating a trigger function that returns a trigger.
As you can se= e, I get a error at the end. Appreciate any help in this regard.
<= br>
-- Create table A

create table A (

var1 varchar(40),=

var2 varchar(40) );



-- Create table B

create table B (

<= span class=3D"">"id" SERIAL PRIMARY KEY,

 name varchar(40));


-- Create= table C

create tabl= e C (

 "id" SERIAL PRIMARY KEY= ,

 name varchar(40)

=

 , b_id integer references B= (id) NOT NULL);


= -- Create a trigger function

CREATE= OR REPLACE FUNCTION fn_test() RETURNS trigger AS $BODY$

DECLARE

a_id in= t;

b_id int;

c_id int;

BEGIN<= /span>

INSERT INTO B (name) VALUES (NEW.va= r1);

b_id :=3D (select id from B wh= ere name =3D 'NEW.var1');

INSERT IN= TO C (name, b_id) VALUES (NEW.var2, b_id);

return NEW;

 END;

$BODY$ LANGUAGE plpgsql;

-- Crea= te trigger

CREATE TRIGGER tr_test

  BEFORE insert or UPDATE<= /span>

  ON A

  FOR EACH ROW

  EXECUTE P= ROCEDURE fn_test();


=

insert into A (var1, var2) values ('Hello', '= World');

ERROR:  null val= ue in column "b_id" violates not-null constraint

DETAIL:  Failing row contains (1, World, null).

CONTEXT:  SQL statement "INSE= RT INTO C (name, b_id) VALUES (NEW.var2, b_id)"

=

PL/pgSQL function fn_test() line 17 at SQL st= atement


On Tue, May 26, 2015 at 9:14 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
re-including the list

On Tue, May 26, 2015 at 9:09 AM, Shekar Tippur <ctippur@gmail.com> wrote:
<= div class=3D"gmail_quote">
=E2=80=8B
On Tue, May= 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@gmail.com= > wrote:
On Tue, May 26, 2015 at 8:45 AM, Shekar Tippur <ctippur@gmail.com> wrote:
=

This is what I am trying:

=  WITH x AS 

(INSERT INTO industry (name,a= bbr,description,cr_date,last_upd) 

VALUES ('df= ','','',now(),now()) returning id) insert into sector (name,description,cr_d= ate,last_upd,industry_id) select 's1','',now(),now(),id from x;
I get a error:

ERROR:  insert or u= pdate on table "sector" violates foreign key constraint "sector_id_fkey"

DETAIL:  Key (id)=3D(394) is not present in table= "industry".

If I execute the insert individually, I= am able to insert a record. Wonder what I am doing wrong.

I have been stuck with this issue for over 24 hours. Appreciate any h= elp.

<= /div>

It is not possible to accomplish your g= oal using a CTE.  =46rom the point of view of both tables the data they= can see is what was present before the statement began.

=
The more usual way to accomplish this is the write a pl/pgsql function w= ith two statements and passing the ID between them using an intermediate var= iable.

=E2=80=8BDavid J.

=E2=80=8B>>>>= ;>>>>>>>>>>>>>>>=E2=80=8B
=  
=E2=80=8B
=E2=80=8B
I have tried that as well.

    &= nbsp;           INSERT INTO industry (name,abbr,des= cription,cr_date,last_upd) VALUES (NEW.industry,'','',now(),now()) returning= id into industry_id;

             =   industry_id :=3D (select industry_id from industry where name =3D= 'NEW.industry');

              &nb= sp; raise notice 'industry id is %', industry_id; 

    &= nbsp;           INSERT INTO sector (name,descriptio= n,cr_date,last_upd,industry_id) VALUES (NEW.sector,'',now(),now(),industry_i= d) returning id into sector_id;

 -- I get a new industry ID bu= t a new row is not inserted. I am guessing this is the case because it takes= all the transactions as atomic. As a result, I get a foreign key violation.=

=E2=80=8B>&= gt;>>>>>>>>>>>>>>>>=E2=80=8B=


=E2=80=8B= If you are using a trigger you should also provide the relevant CREATE TRIGG= ER statement...
<= br>
In fact, you r= eally you supply a self-contained example.

Also, please do not top-post.

David J.
=E2=80=8B



= --Apple-Mail-DFD02705-C6B1-445D-91C7-CB17C2D0A10E--