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
Unfortunately, PostgreSQL doesn't really support the SQL standard
MULTISET
operator, nor nested sets in general. You could create anARRAY
ofROW
types like this:And you could even unnest the above array
So, if an
ARRAY
ofROW
is acceptable to you, you could write something like this:If you have your own
OBJECT
type in PostgreSQL, you can cast the anonymousROW
to your type: