Re: Postgres CTE issues
| От | Shekar Tippur |
|---|---|
| Тема | Re: Postgres CTE issues |
| Дата | |
| Msg-id | CAHmfAToeCWtdGcdtgPVJtXQxG1CWLwW1BKKoW=BT9zpR-p24TQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Fwd: Postgres CTE issues ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: Postgres CTE issues
(daku.sandor@gmail.com)
|
| Список | pgsql-sql |
create table A (
var1 varchar(40),
var2 varchar(40) );
create table B (
"id" SERIAL PRIMARY KEY,
name varchar(40));
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
re-including the listOn 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 по дате отправления: