1

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?

1
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.