Re: Postgres CTE issues - daku.sandor@gmail.com

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: daku.sandor@gmail.com
To: Shekar Tippur <ctippur@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Postgres CTE issues
Date: Wed, 27 May 2015 08:24:00 +0200
Message-ID: <355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com> (raw)
In-Reply-To: <CAHmfAToeCWtdGcdtgPVJtXQxG1CWLwW1BKKoW=BT9zpR-p24TQ@mail.gmail.com>
References: <CAHmfATopobYPPVb5HRS-wZfuKkXLcz7gmbcx6bFpsKs1KE92-A@mail.gmail.com>
	<CAKFQuwbMEZFiNKiaCKUvvoS854bkE3Dqa288vcGYTg3DtK09Og@mail.gmail.com>
	<CAHmfATorjD9ieWk1U5ZAzSSrmpiCvWOdCtMof620JUQTmMugFA@mail.gmail.com>
	<CAKFQuwaTZ8q0yBnEdkBjKtw4dzK9ntOSvVtSUxAa1fXMYNpsfg@mail.gmail.com>
	<CAKFQuwb-H0SfLRwMCBAxfo4bwsB8X4MmKxgmqOqCNW7hpVWxHQ@mail.gmail.com>
	<CAHmfAToeCWtdGcdtgPVJtXQxG1CWLwW1BKKoW=BT9zpR-p24TQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

 
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 <ctippur@gmail.com> wrote:
> 
> 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 regard.
> 
> -- Create table A
> 
> create table A (
> 
> var1 varchar(40),
> 
> var2 varchar(40) );
> 
> 
> 
> -- Create table B
> 
> create table B (
> 
> "id" SERIAL PRIMARY KEY,
> 
>  name varchar(40));
> 
> 
> -- Create table C
> 
> create table 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 int;
> 
> b_id int;
> 
> c_id int;
> 
> BEGIN
> 
> INSERT INTO B (name) VALUES (NEW.var1);
> 
> b_id := (select id from B where name = 'NEW.var1');
> 
> INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id);
> 
> 
> return NEW;
> 
>  END;
> 
> $BODY$ LANGUAGE plpgsql;
> 
> -- Create trigger
> 
> CREATE TRIGGER tr_test
> 
>   BEFORE insert or UPDATE
> 
>   ON A
> 
>   FOR EACH ROW
> 
> 
>   EXECUTE PROCEDURE fn_test();
> 
> 
> 
> insert into A (var1, var2) values ('Hello', 'World');
> 
> ERROR:  null value in column "b_id" violates not-null constraint
> 
> DETAIL:  Failing row contains (1, World, null).
> 
> CONTEXT:  SQL statement "INSERT INTO C (name, b_id) VALUES (NEW.var2, b_id)"
> 
> PL/pgSQL function fn_test() line 17 at SQL statement
> 
> 
>> 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:
>> 
>>> 
>>>> 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,abbr,description,cr_date,last_upd) 
>>>>> 
>>>>> VALUES ('df','','',now(),now()) returning id) insert into sector (name,description,cr_date,last_upd,industry_id) select 's1','',now(),now(),id from x;
>>>>> 
>>>>> I get a error:
>>>>> 
>>>>> ERROR:  insert or update on table "sector" violates foreign key constraint "sector_id_fkey"
>>>>> 
>>>>> DETAIL:  Key (id)=(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 help.
>>>> 
>>>> It is not possible to accomplish your goal using a CTE.  From 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 with two statements and passing the ID between them using an intermediate variable.
>>>> 
>>>> David J.
>> >>>>>>>>>>>>>>>>>>> 
>> I have tried that as well.
>> 
>>                 INSERT INTO industry (name,abbr,description,cr_date,last_upd) VALUES (NEW.industry,'','',now(),now()) returning id into industry_id;
>> 
>>                 industry_id := (select industry_id from industry where name = 'NEW.industry');
>> 
>>                 raise notice 'industry id is %', industry_id; 
>> 
>>                 INSERT INTO sector (name,description,cr_date,last_upd,industry_id) VALUES (NEW.sector,'',now(),now(),industry_id) returning id into sector_id;
>> 
>>  -- I get a new industry ID but 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.
>> 
>> 
>> >>>>>>>>>>>>>>>>>>
>> 
>> If you are using a trigger you should also provide the relevant CREATE TRIGGER statement...
>> 
>> In fact, you really you supply a self-contained example.
>> 
>> Also, please do not top-post.
>> 
>> David J.
>

view thread (9+ messages)

Message-ID: <355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com>
Permalink:  ../355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com/
Also on:    postgresql.org/message-id/355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: daku.sandor@gmail.com, ctippur@gmail.com
  Subject: Re: Postgres CTE issues
  In-Reply-To: <355E8876-6CD0-4183-A969-674EC3848CEA@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox