Fwd: Postgres CTE issues
| От | David G. Johnston |
|---|---|
| Тема | Fwd: Postgres CTE issues |
| Дата | |
| Msg-id | CAKFQuwb-H0SfLRwMCBAxfo4bwsB8X4MmKxgmqOqCNW7hpVWxHQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Postgres CTE issues (Shekar Tippur <ctippur@gmail.com>) |
| Ответы |
Re: Postgres CTE issues
|
| Список | pgsql-sql |
re-including the list
On Tue, May 26, 2015 at 9:00 AM, David G. Johnston <david.g.johnston@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.
В списке pgsql-sql по дате отправления: