I have a array of user-defined composite data type. I need to do some manipulation on the array elements in plpgsql function but I'm not getting the syntax right to access the individual elements. Any help is appreciated. Pasted below is simplified version of the code.
CREATE TYPE playz AS(
a integer,
b numeric,
c integer,
d numeric);
CREATE OR REPLACE FUNCTION playx(OUT mod playz[]) AS $$
BEGIN
FOR i in 1..5 LOOP
mod[i].a = 1;
mod[i].b = 12.2;
mod[i].c = 1;
mod[i].d = 0.02;
END LOOP;
END;
$$ LANGUAGE plpgsql;
I get the following error when I try to execute this.
ERROR: syntax error at or near "." LINE 5: mod[i].a = 1;
I'm using Postgres 9.2
The left expressions must be pretty simply in PLpgSQL. The combination of array and composite type is not supported. You should to set a value of composite type, and then this value assign to array.
CREATE OR REPLACE FUNCTION playx(OUT mod playz[]) AS $$
DECLARE r playz;
BEGIN
FOR i in 1..5 LOOP
r.a = 1;
r.b = 12.2;
r.c = 1;
r.d = 0.02;
mod[i] = r;
END LOOP;
END;
$$ LANGUAGE plpgsql;
There is possible a shortcut:
CREATE OR REPLACE FUNCTION public.playx(OUT mod playz[])
LANGUAGE plpgsql
AS $function$
BEGIN
FOR i in 1..5 LOOP
mod[i] = ROW(1, 12.2, 1, 0.02);
END LOOP;
END;
$function$;