Take a table
CREATE TABLE foo(a,b,c,d) AS VALUES (1,2,3,4);
I can insert into
foo with a row type..
INSERT INTO foo SELECT (foo).* FROM foo; INSERT INTO foo SELECT (v).* FROM ( VALUES (42,42,42,42) ) AS v;
But I can't update foo with a row type.
-- fails UPDATE foo SET (a,b,c,d) = ( SELECT ROW(1,2,3,4) ).*; -- also fails. UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4)); ERROR: number of columns does not match number of values LINE 1: UPDATE foo SET (a,b,c,d) = (SELECT (1,2,3,4));
Is there any syntax to update an entire row with a composite type. The really weird thing is that this works in a trigger with
NEW, and I'm trying to write the same code without a trigger.
CREATE OR REPLACE FUNCTION foo() RETURNS trigger AS $$ BEGIN NEW=(42,42,42,42); RETURN NEW; END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE TRIGGER ok BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE foo(); -- The NEW row in trigger gets updated with (42,42,42,42) -- the UPDATE succeeds; all rows (42,42,42,42) UPDATE foo SET (a,b,c,d) = (1,2,3,4);
You can see here that we can update the row object inside the procedure, and change the update. However, we can't do that in the
UPDATE ddl? I would expect the RECORD produced by the same name of the table to work the same way.
-- Such as this, UPDATE foo SET foo = (1,2,3,4);