Tengo un procedimiento almacenado en el que estoy trabajando que usa un conjunto de tipos compuestos para devolver lo que me estaba tomando 4 consultas diferentes antes. El problema con el que me encuentro es que algunos de los elementos no funcionan como se esperaba. Dada una estructura tipo como la siguiente:
CREATE TYPE mytype3 ( field1 TEXT, field2 TEXT ); CREATE TYPE mytype2 ( field1 INTEGER, field2 mytype3[] ); CREATE TYPE mytype1 ( field1 TEXT, field2 mytype2[] );¿Cómo abordaría un elemento espacial "mytype3"? Yo esperaría que fuera algo como:
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;Pero esto parece tener un error de sintaxis en el primer índice de matriz. Leí mucho los documentos y busqué en Google, pero nada parece entrar en un UDT tan complejo.
Esta es una de las funciones que realmente faltan en PLpgSQL. La declaración de asignación no admite la expresión compleja de la parte izquierda. En el lado izquierdo puede estar el campo de tipo de registro o compuesto o el campo de matriz. Pero la mezcla no son compatibles. Deberías usar variables auxiliares:
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] := ..Además, su código es incorrecto: a) está utilizando tipos de tipos (mytypex) como nombres de campo. b) sus tipos son cíclicos - mytype1 usa mytype2 y mytype2 usa mytype1
consulte https://www.postgresql.org/docs/current/static/plpgsql-statements.html - Asignación: como se explicó anteriormente, la expresión en dicha declaración se evalúa mediante un comando SQL SELECT enviado al motor de base de datos principal . La expresión debe producir un solo valor (posiblemente un valor de fila, si la variable es una variable de fila o de registro). La variable de destino puede ser una variable simple (opcionalmente calificada con un nombre de bloque), un campo de una variable de fila o registro, o un elemento de una matriz que es una variable o campo simple. Se puede usar igual (=) en lugar de compatible con PL/SQL :=.
El lenguaje PLpgSQL es un lenguaje bastante estático y el uso de estructuras demasiado anidadas es una mala práctica (y en PG anteriores a 9.5 puede ser muy lento).