Re: Postgres CTE issues

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
> ​
>
>
>