Is there anyway to UPDATE a series of columns on a row with a composite type record/row?

Question

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);

Answer 1

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 } [, ...] ) |

Answer 2

How about (server 9.5.7) ...

create table foo(a,b,c,d)
as values
  (1,2,3,4)
, (4,5,6,7)
, (8,9,0,null);

postgres=# select * from foo;
 a | b | c | d 
---+---+---+---
 1 | 2 | 3 | 4
 4 | 5 | 6 | 7
 8 | 9 | 0 |  
(3 rows)


update foo
set (a,b,c,d) = ( 
  select (v).*
  from ( values (42,42,42,42) ) as v 
) ;

postgres=# select * from foo;
 a  | b  | c  | d  
----+----+----+----
 42 | 42 | 42 | 42
 42 | 42 | 42 | 42
 42 | 42 | 42 | 42
(3 rows)

Dbfiddle here.

Or, indeed (without trigger):

postgres=# update foo
postgres-# set (a,b,c,d) = (11,22,33,44) ;
UPDATE 3
postgres=# select * from foo;
 a  | b  | c  | d  
----+----+----+----
 11 | 22 | 33 | 44
 11 | 22 | 33 | 44
 11 | 22 | 33 | 44
(3 rows)

Answer 3

This seems to work (in versions 9.5 and 9.6) but I'm not if it will solve your issue.

• convert the row into type foo:

  row(1,2,3,4)::foo
  

• expand it into columns:

  select ( ).*
  

The whole code:

update foo set (a,b,c,d) = (select (row(1,2,3,4)::foo).*) ;