Fwd: Postgres CTE issues

re-including the list

On Tue, May 26, 2015 at 9:09 AM, Shekar Tippur <ctippur(at)gmail(dot)com> wrote:

> ​
> On Tue, May 26, 2015 at 9:00 AM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Tue, May 26, 2015 at 8:45 AM, Shekar Tippur <ctippur(at)gmail(dot)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.
​