PostgreSQL: Trigger INSERT INTO SELECT from other table-CodePudding

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.

CodePudding user response：

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;