CODEWITHSUNDEEP
sqlpostgresqlfunctiontriggersinsert
Eda Avcuoğlu
Eda Avcuoğlu

Reputation: 1

How can I make it insert one by one while loading two data at the same time?

INSERT INTO tbl_product_attribute_value (db_id,db_product_id,db_category_id,db_attribute_id,db_attribute_value_id, db_value)
VALUES
     (1999,217,2264,47,null , 'a'),
     (2000,217,2264,47,null , 'b')
limit 1;
RETURNING * ;

I want to add a limit to this query after insert trigger. Because I have this error because of the query

SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression Where: SQL statement "SELECT (select db_value from tbl_product_attribute_value where (db_attribute_id = 47 and db_attribute_value_id is null) )" PL/pgSQL function deneme2() line 7 at assignment

And I have a trigger for the insert function

 create or replace function deneme2() 
 returns trigger 
 as $$
 declare 
 idd integer;
 name varchar;
 begin 
    idd := (SELECT MAX(db_id)+1 FROM public.tbl_attribute_value);
 name := (select db_value from  tbl_product_attribute_value where (db_attribute_id = 47 and db_attribute_value_id is null) );
 insert into tbl_attribute_value (db_id, db_name) values (idd,name);
    update tbl_product_attribute_value  SET db_attribute_value_id = idd  where (db_attribute_id = 47 and db_attribute_value_id is null);
    return new;
 end;
 $$
 language plpgsql;

 create trigger assign_id_attribute_value
 after INSERT
 on tbl_product_attribute_value
 for each row 
 execute procedure deneme2();

Upvotes: 0

Views: 32

Answers (1)

nbk
nbk

Reputation: 49410

instead of using a variable use POSTGRES INSERT INTO SELECT

You find it in the examples https://www.postgresql.org/docs/current/sql-insert.html

 create or replace function deneme2() 
 returns trigger 
 as $$
 declare 
     idd integer;
 begin 
    idd := (SELECT MAX(db_id)+1 FROM public.tbl_attribute_value);
 
 insert into tbl_attribute_value (db_id, db_name) select idd,db_value from  tbl_product_attribute_value where (db_attribute_id = 47 and db_attribute_value_id is null);
    update tbl_product_attribute_value  SET db_attribute_value_id = idd  where (db_attribute_id = 47 and db_attribute_value_id is null);
    return new;
 end;
 $$
 language plpgsql;

Better would be to use a serial auto increment

But with concurrent inserts, you could get problems, that two or more have then the same idd number, so would need to use a transaction, to prveent that

Upvotes: 0

Related Questions