0

I am using Postgres 13 version and seeing strange behaviour on Instead of Insert trigger. In the output of Insert trigger Default values are NULL. But the value is provided in column, how should I change return NEW from trigger to have that value?

CREATE TABLE test1
(test_id UUID NOT NULL DEFAULT uuid_generate_v4(),
testname VARCHAR(128));

CREATE TABLE test2
(test_id UUID NOT NULL DEFAULT uuid_generate_v4(),
testname VARCHAR(128));

CREATE VIEW test_view
AS 
    SELECT * 
    FROM test1 
    UNION ALL 
    SELECT * 
    FROM test2;

CREATE OR REPLACE FUNCTION public.insert_test()
  RETURNS trigger
   SECURITY DEFINER
   AS
$func$
BEGIN
    --some functionality understanding where to insert records
    IF 1 = 1 THEN
      INSERT INTO public.test1
      (
          testname
       )
      VALUES
      (NEW.testname
      );
    ELSE
      INSERT INTO public.test2
      (
          testname
       )
      VALUES
      (NEW.testname
      );
    END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER TRG_test_migration_insert
INSTEAD OF INSERT ON public.test_view
FOR EACH ROW EXECUTE PROCEDURE public.insert_test();
 

INSERT INTO test_view
(testname)
VALUES
('abc')
RETURNING *;

INSERT INTO test1
(testname)
VALUES
('def')
RETURNING *;

So the returning will be different, how I could modify the trigger to have default value in the returning? I've tried to have something

RETURN SELECT v_test_id, new.testname 

but it did not work. THank you!

1 Answer 1

1

You need to return a ROW that matches the view structure so something like:

RETURN ROW('a5f1cbf5-a35a-4390-9ee3-536b607d40c2'::uuid, NEW.testname)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.