Oracle Cast and MULTISET are available on POSTGRES
I was working on a create xml query from an oracle database
where "column" is the type
CREATE OR REPLACE TYPE "column" AS OBJECT
("coulmnname" VARCHAR2 (30), "datatype" VARCHAR2 (30))
and col_list_t are 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 the problem is that this has to be converted to postgres as CAST and MULTISET are not available in postgres, so is there a way to do this in postgres syntax
source to share
Unfortunately, PostgreSQL doesn't actually support the SQL standard MULTISET
, and not nested sets in general. You can create ARRAY
from ROW
types like this:
select array[row(1, 2), row(3, 4)]
And you can even turn off the above array
select * from unnest(array[row(1, 2), row(3, 4)]) t(a int, b int)
So, if a ARRAY
of is ROW
acceptable to you, you can write something like this:
select array_agg(row(a, b))
from (
select ...
) t(a, b)
If you have your own type OBJECT
in PostgreSQL, you can cast anonymous ROW
to your type:
select array_agg(row(a, b)::your_type)
from (
select ...
) t(a, b)
source to share