I have a stored procedure I'm working on which uses a set of composite types to return what was taking me 4 different queries before. The problem I'm running into is addressing some of the elements aren't working as expected. Given a type-structure like the following:
CREATE TYPE mytype3 ( field1 TEXT, field2 TEXT );
CREATE TYPE mytype2 ( field1 INTEGER, field2 mytype3[] );
CREATE TYPE mytype1 ( field1 TEXT, field2 mytype2[] );
how would I address a spacific "mytype3" element? I would expect it to be something like:
CREATE FUNCTION get_item( IN n VARCHAR(64) )RETURNS mytype1 AS $$
DECLARE
iid INTEGER;
r RECORD;
output mytype1;
BEGIN
iid=(SELECT id FROM idlist WHERE name=n LIMIT 1);
SELECT field1, NULL FROM table1 WHERE id=iid LIMIT 1 INTO output;
SELECT array( SELECT (field2, NULL) FROM table2 WHERE id=iid)
AS foo INTO output.field2;
FOR r IN SELECT id,field1,field2 FROM table3 WHERE id=iid LOOP
output.field2[r.id].field2 :=
array_append(output.field2[r.id].field2, (r.field1, r.field2));
END LOOP;
RETURN output;
END
$$ LANGUAGE plpgsql STABLE;
But this seems to have a syntax error at the first array-index. I've done a lot of reading of the docs and googling, but nothing seems to go into a UDT this complex.
This is one from really missing features in PLpgSQL. The assign statement doesn't support complex left part expression. On left side can be record or composite type's field or array field. But the mix are not supported. You should to use auxiliary variables:
DECLARE
var1 customtype;
var2 customtype[];
BEGIN
...
FOR r IN SELECT ...
-- left part can be (only these simple variants are supported)
var1.field := ..
var2[r.id] := ..
More, your code is wrong - a) you are using types types (mytypex) as field names. b) your types are cyclic - mytype1 uses mytype2 and mytype2 uses mytype1
see https://www.postgresql.org/docs/current/static/plpgsql-statements.html - Assignment - As explained previously, the expression in such a statement is evaluated by means of an SQL SELECT command sent to the main database engine. The expression must yield a single value (possibly a row value, if the variable is a row or record variable). The target variable can be a simple variable (optionally qualified with a block name), a field of a row or record variable, or an element of an array that is a simple variable or field. Equal (=) can be used instead of PL/SQL-compliant :=.
PLpgSQL language is pretty static language and using too nested structures is is bad practice (and on older PG than 9.5 can be very slow).