Is there anyway to UPDATE a series of columns on a row with a composite type record/row? 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);
There are actually lots of posts regarding this..


Support UPDATE table SET(*)=...
WIP patch for multiple column assignment in UPDATE
Supporting multiple column assignment in UPDATE (9.5 project)
Better handling of UPDATE multiple-assignment row expressions


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

UPDATE foo SET (a,b,c,d) = ROW(10,9,8,7);


The docs don't say much about this


  According to the standard, the source value for a parenthesized sub-list of target column names can be any row-valued expression yielding the correct number of columns. **PostgreSQL only allows the source value to be a row constructor or a sub-SELECT. **An individual column's updated value can be specified as DEFAULT in the row-constructor case, but not inside a sub-SELECT.


But you can also see at the syntax for UPDATE

   ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |