Assigning a single value to an array of a composite type by referencing a column name

Serhad Erdem 0
Viewed 27

Do you have any opinion?

CREATE OR REPLACE FUNCTION fnc_test()
   RETURNs integer
AS
$$
declare
   tb_var dba_reports[];
   v_serialno int := 1;
begin
   SELECT array_agg(tbl) into tb_var FROM "dba_reports" tbl;
   RAISE NOTICE '%', tb_var[v_serialno].report_id;
   tb_var[v_serialno].report_id :=99
   
   return 1;
end;
$$
language plpgsql;

ERROR:  syntax error at or near "."
LINE 14:    tb_var[v_serialno].report_id :=99
plpgsql postgresql arrays
1 Answers

You need an auxiliary variable:

...
declare
   tb_var dba_reports[];
   report dba_reports;
   v_serialno int := 1;
begin
   SELECT array_agg(tbl) into tb_var FROM "dba_reports" tbl;
   RAISE NOTICE '%', tb_var[v_serialno].report_id;
   report := tb_var[v_serialno];
   report.report_id := 99;
   tb_var[v_serialno] := report;
...

See however my opinion on the arrays of composite types in the answer and comments in How do you store double quotes in Postgres array of composite type?

klin0
Related