PostgreSQL: Trigger INSERT INTO SELECT from other table

Question

I'm trying to create a trigger that will add a new row processed entry to a destination table each time a new row is created in the source table.

Step 1 Create destination table:

CREATE TABLE public.destination_table (
                          id serial PRIMARY KEY,
                          created_at TIMESTAMP NOT NULL,
                          sale_id INTEGER NOT NULL,
                          product_id INTEGER NOT NULL,
                          product_name VARCHAR NOT NULL,
                          url VARCHAR NOT NULL, 
                          shop_id VARCHAR NOT NULL,
                          user_id VARCHAR)

Step 2 Create trigger function:

CREATE OR REPLACE FUNCTION triger_function() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO public.destination_table ( created_at, sale_id, product_id, product_name, url, shop_id, user_id)
 SELECT created_at,
        sale_id,
        product_id,
        product_name,
        split_part(url::text, '?'::text, 1) AS url,
        shop_id,
        ((((((((data #>> '{}'::text[])::jsonb) #>> '{}'::text[])::jsonb) -> 'local_storage'::text) -> 'data'::text) #>> '{}'::text[])::jsonb) ->> 'user_id'::varchar AS user_id
   FROM source_table;
           RETURN new;
END;
$BODY$
language plpgsql;

** The Select query inside function work normally when single running.

Step 3 Create trigger:

CREATE TRIGGER trigger_records
     AFTER INSERT ON public.source_table
     FOR EACH ROW
     EXECUTE PROCEDURE triger_function();

The problem is that Trigger does not work, which means it does not record new entries in the target table. Can't figure out where the error is.

1 ) The problem is you did not read this plpgsql trigger function 2) The SELECT portion of the query has no WHERE so you would be selecting all rows from source_table. 3) You don't need the SELECT because if you had done 1) you would know that you have the newly entered values available as NEW.* e.g. NEW.created_at, NEW.sale_id, NEW.product_id, etc — Adrian Klaver
– Adrian Klaver, Commented May 26, 2022 at 15:22

cogitoergosum · Accepted Answer · 2022-05-26 15:26:52Z

3

You should be using the NEW record in the trigger function to reference the newly inserted data instead of a select, i.e.:

CREATE OR REPLACE FUNCTION triger_function() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO public.destination_table ( created_at, sale_id, product_id, product_name, url, shop_id, user_id)
VALUES(NEW.created_at,
    NEW.sale_id,
    NEW.product_id,
    NEW.product_name,
    split_part(NEW.url::text, '?'::text, 1),
    NEW.shop_id,
    ((((((((NEW.data #>> '{}'::text[])::jsonb) #>> '{}'::text[])::jsonb) -> 'local_storage'::text) -> 'data'::text) #>> '{}'::text[])::jsonb) ->> 'user_id'::varchar)
       RETURN new;
END;
$BODY$
language plpgsql;

answered May 26, 2022 at 15:26

cogitoergosum

6916 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Trigger INSERT INTO SELECT from other table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related