6

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

SarthAk
  • 1,628
  • 3
  • 19
  • 24
  • `cast(...)` *is* available in Postgres, but `multiset` is not. If you are generating XML, did you look at the various XML functions in Postgres? –  Dec 02 '14 at 10:29

1 Answers1

10

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)
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • 5
    I'm always a little shocked when there's a useful SQL-standard way of doing something that Oracle supports and PostgreSQL doesn't. Oracle not being known for its love of doing things the standard way. At a quick glance, multisets look exceedingly useful. – Craig Ringer Dec 02 '14 at 11:14
  • @CraigRinger: I'm very surprised myself. Of course, much of the SQL standard has been driven by Oracle and IBM in the past, so often, it's not really surprising to find Oracle features in there - e.g. also ordered aggregate functions via `WITHIN GROUP (ORDER BY ...)`. In this case, Oracle doesn't fully implement the standard, but requires the OP's `CAST` to a named collection type. In the standard, collections and arrays can be anonymous as well, as implemented by Informix, for example – Lukas Eder Dec 02 '14 at 11:52
  • @CraigRinger: Another point for Oracle vs. PostgreSQL: `MERGE` – Lukas Eder Dec 02 '14 at 11:53
  • Yeah, MERGE is ... well, a mixed bag. It's a great OLAP data merging tool, but it often gets incorrectly abused for OLTP UPSERT-style use, where it's just plain broken. It doesn't replace proper upserts. – Craig Ringer Dec 02 '14 at 12:40
  • @CraigRinger: It depends on the database. In theory, there's no such thing as abuse :-) – Lukas Eder Dec 02 '14 at 13:22
  • What about nowdays? Did anything change? I could not find any significant changes on the internet unfortunately. – Tobias Marschall Aug 24 '18 at 14:22
  • @TobiasMarschall: Nope – Lukas Eder Aug 24 '18 at 14:41