使用string_to_array()对PostgreSQL查询转换的Oracle

I have below query in Oracle:

我在Oracle中有以下查询:

SELECT to_number(a.v_VALUE), b.v_VALUE
       FROM TABLE(inv_fn_splitondelimiter('12;5;25;10',';')) a
       JOIN TABLE(inv_fn_splitondelimiter('10;20;;', ';')) b
         ON a.v_idx = b.v_idx

which give me result like:

给了我这样的结果:

I want to convert the query to Postgres. I have tried a query like:

我想将查询转换为Postgres。我尝试过这样的查询:

SELECT UNNEST(String_To_Array('10;20;',';'))

I have also tried:

我也试过:

SELECT a,b
       FROM (select  UNNEST(String_To_Array('12;5;25;10;2',';'))) a
       LEFT JOIN (select  UNNEST(String_To_Array('12;5;25;10',';'))) b
         ON a = b

But didn't get a correct result.
I don't know how to write query that's fully equivalent to the Oracle version. Anyone?

但是没有得到正确的结果。我不知道如何编写与Oracle版本完全相同的查询。有人知道吗?

2 个解决方案

#1

In the expression select a the a is not a column, but the name of the table alias. Consequently that expressions selects a complete row-tuple (albeit with just a single column), not a single column.

在表达式中选择a不是列，而是表别名的名称。因此，表达式选择一个完整的行元组(尽管只包含一个列)，而不是单个列。

You need to define proper column aliases for the derived tables. It is also recommended to use set returning functions only in the from clause, not in the select list.

您需要为派生表定义适当的列别名。还建议只在from子句中使用set return函数，而不是在select列表中。

If you are not on 9.4 you need to generate the "index" using a window function. If you are on 9.4 then Erwin's answer is much better.

如果您不是在9.4，您需要使用窗口函数生成“索引”。如果你是在9.4，那么Erwin的答案会更好。

SELECT a.v_value, b.v_value
FROM (
   select row_number() over () as idx,  -- generate an index for each element
          i as v_value
   from UNNEST(String_To_Array('12;5;25;10;2',';')) i
) as a 
  JOIN (
     select row_number() over() as idx, 
            i as v_value
     from UNNEST(String_To_Array('10;20;;',';')) i
  ) as b 
  ON a.idx = b.idx;

An alternative way in 9.4 would be to use the with ordinality option to generate the row index in case you do need the index value:

9.4中的另一种方法是使用具有序号的选项来生成行索引，以防您需要索引值:

select a.v_value, b.v_value
from regexp_split_to_table('12;5;25;10;2',';') with ordinality as a(v_value, idx)
  left join regexp_split_to_table('10;20;;',';') with ordinality as b(v_value, idx) 
    on a.idx = b.idx

#2

Starting with Postgres 9.4 you can use unnest() with multiple arrays to unnest them in parallel:

从Postgres 9.4开始，您可以使用unnest()使用多个数组，以并行地将它们拆解:

SELECT *
FROM   unnest('{12,5,25,10,2}'::int[]
            , '{10,20}'       ::int[]) AS t(col1, col2);

That's all. NULL values are filled in automatically for missing elements to the right.

这是所有。空值被自动填充到右侧的缺失元素。

If parameters are provided as strings, convert with string_to_array() first. Like:

如果将参数作为字符串提供，则首先使用string_to_array()进行转换。如:

SELECT *
FROM   unnest(string_to_array('12;5;25;10', ';')
            , string_to_array('10;20'     , ';')) AS t(col1, col2);

More details and an alternative solution for older versions:

更多的细节和旧版本的替代解决方案:

• Unnest multiple arrays in parallel
• 将多个数组以并行方式打开。
• Split given string and prepare case statement
• 拆分给定字符串并准备case语句。