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!