Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (
  tbl_id int
, foo_a text
, foo_b text
, foo_c text
, bar_a text
);

INSERT INTO tbl VALUES
  (1, 'a1','b1','c1','d1')
, (2, 'a2','b2','c2','d2')
, (3, 'a3','b3','c3','d3')
;
3 rows affected

CREATE OR REPLACE FUNCTION f_update_cols(_tbl regclass, _col_pattern text
                                       , OUT row_ct int, OUT col_ct bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT INTO _sql, col_ct
          format('UPDATE tbl SET (%s) = (%s)'
               , string_agg (quote_ident(attname), ', ')
               , string_agg ('NULL', ', ')
                )
        , count(*)
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped            -- no dropped columns
   AND    attnum > 0                  -- no system columns
   AND    attname LIKE _col_pattern;  -- only columns matching pattern

   -- RAISE NOTICE '%', _sql;         -- output SQL for debugging
   EXECUTE _sql;

   GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$;

COMMENT ON FUNCTION f_update_cols(regclass, text)
IS 'Updates all columns of table _tbl ($1)
that match _col_pattern ($2) in a LIKE expression.
Returns the count of columns (col_ct) and rows (row_ct) affected.';
TABLE tbl;
SELECT * FROM f_update_cols('tbl', 'foo%');
TABLE tbl;


tbl_id
foo_a
foo_b
foo_c
bar_a


1
a1
b1
c1
d1

2
a2
b2
c2
d2

3
a3
b3
c3
d3




row_ct
col_ct


3
3




tbl_id
foo_a
foo_b
foo_c
bar_a


1
null
null
null
d1

2
null
null
null
d2

3
null
null
null
d3

tbl_id	foo_a	foo_b	foo_c	bar_a
1	null	null	null	d1
2	null	null	null	d2
3	null	null	null	d3