Re: Postgres CTE issues

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(at)gmail(dot)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(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.
>