Assign a value to a field of a composite type (a row in an array of rows)?

Question

I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'm having.

drop type if exists test_type;

create type test_type as (
  some_int int
);

create or replace function test()
returns void as
$$
declare
x test_type[];
y test_type;
begin
  x[1] = row(1);
  y = row(1);

  /* DOESN'T WORK */
  /* ERROR: syntax error at or near "." */
  /* x[1].some_int = 2; */

  /* HOWEVER, THIS WORKS */
  y = x[1];
  y.some_int = 2;
  x[1] = y;


  raise notice '%', x[1].some_int;
end;
$$
language plpgsql;

As you can see, referencing x[1].some_int is perfectly legal in most contexts but not for assignment. The workaround accomplishes the goal; however, it involves two additional copies via the assignment operator. Is there any explanation for why this is an error or how to work around it and accomplish this task without creating extra row copies?

Answer 1

Yes, this feature is missing in the arsenal of array functions. It's rather exotic, and one would typically operate with a (temporary) table instead of an array of rows.

That said, there is a way with a single assignment, using the hstore #= operator:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS int LANGUAGE plpgsql AS
$func$
DECLARE
   x test_type[] := '{(1)}';
BEGIN
   x[1] := x[1] #= hstore '"some_int"=>"2"'; -- !!!
   RETURN x[1].some_int;
END
$func$;

SELECT f_test();

| f_test |
| -----: |
|      2 |

db<>fiddle here

The additional module hstore must be installed. In-depth explanation:

• How to set value of composite variable field using dynamic SQL