Oracle Cast和MULTISET可用于POSTGRES

本文介绍了Oracle Cast和MULTISET可用于POSTGRES的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我一直在从一个查询生成xml从oracle数据库数据库

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

其中列是一个类型

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

且col_list_t的类型为

and col_list_t is of type

CREATE OR REPLACE TYPE col_list_t AS TABLE OF "column"

和

  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

，问题是这必须转换为postgres作为CAST和MULTISET在postgres中是不可用的，所以在postgres语法中有任何办法这样做

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

推荐答案

不幸的是，PostgreSQL不真正支持SQL标准 MULTISET 运算符，一般也不支持嵌套集。您可以创建 ARRAY ROW 类型：

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)

如果您可以接受 ARRAY 的 ROW ，您可以这样写：

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)

如果您有自己的 OBJECT 键入PostgreSQL，可以将匿名 ROW 转换为您的类型：

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)

这篇关于Oracle Cast和MULTISET可用于POSTGRES的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Postgres