Oracle Cast and MULTISET avaliable in POSTGRES

Question

I have been working on a query to generate xml from the oracle database database

where "column" is a type

CREATE OR REPLACE TYPE "column" AS OBJECT
                  ("coulmnname" VARCHAR2 (30), "datatype" VARCHAR2 (30))

and col_list_t is of type

CREATE OR REPLACE TYPE col_list_t AS TABLE OF "column"

and

  SELECT CAST (
                      MULTISET (
                           SELECT "column" (B.COLUMN_NAME, B.DATA_TYPE)
                             FROM all_tab_columns b, all_tables c ,all_tables a
                            WHERE     b.TABLE_NAME = a.TABLE_NAME
                                  AND b.table_name = c.TABLE_NAME
                                  AND B.OWNER = C.OWNER
                                  AND c.OWNER = USER)AS col_list_t)  from dual 

and problem is that this has to be converted into postgres as CAST and MULTISET are not avaliable in postgres so is there any way around to do this in postgres syntax

Answer 1

Unfortunately, PostgreSQL doesn't really support the SQL standard MULTISET operator, nor nested sets in general. You could create an ARRAY of ROW types like this:

select array[row(1, 2), row(3, 4)]

And you could even unnest the above array

select * from unnest(array[row(1, 2), row(3, 4)]) t(a int, b int)

So, if an ARRAY of ROW is acceptable to you, you could write something like this:

select array_agg(row(a, b))
from (
  select ...
) t(a, b)

If you have your own OBJECT type in PostgreSQL, you can cast the anonymous ROW to your type:

select array_agg(row(a, b)::your_type)
from (
  select ...
) t(a, b)