Take a table foo
,
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);
Best Answer
There are actually lots of posts regarding this..
However, according to /u/pstef, one of these is committed, and this syntax now works on HEAD, though @ypercubeᵀᴹ reports it does not work on 9.6.5
The docs don't say much about this
But you can also see at the syntax for UPDATE