nested types on EDB

Question

I'm having problems on a migration of plsql application to edb. The current problem is, than we have nested types, like:

 TYPE personas_rt IS RECORD (adulto numeric, nino numeric);  
 TYPE personas_aat IS TABLE OF personas_rt   INDEX BY BINARY_INTEGER;
 TYPE room_type_rt IS RECORD (id numeric, personas_aa personas_aat);
 TYPE room_type_aat  IS TABLE OF room_type_rt         INDEX BY BINARY_INTEGER;

and in oracle plsql procedure, i can do something like:

 hab_list (1).personas_aa (1).adulto := 2;

my question iis, how can do this in edb?

I tried to put parentheses and brackets everywhere, but I can't find the solution.

It is surely a very silly question, but could you tell me the syntax to do this?

Thank you

score 1 · Answer 1 · answered Mar 09 '21 at 13:04

You could create types like this in PostgreSQL:

CREATE TYPE personas_rt AS (adulto bigint, nino bigint);
CREATE TYPE room_type_rt AS (id bigint, personas_aa personas_rt[]);

Then if you have a record hab_list of type room_type_rt[], you could do this in PL/pgSQL:

((hab_list[1]).personal_aa[1]).adulto := 2;

But often it is a good idea to unravel this unspeakable mess of nesting, which does not go well with relational database design.

nested types on EDB

1 Answers1