CODEWITHSUNDEEP
sqloracle-databasepostgresqlset-returning-functionsunnest
Dhaval Patel
Dhaval Patel

Reputation: 7601

Oracle to PostgreSQL query conversion with string_to_array()

I have below query in 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:

enter image description here

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

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?

Upvotes: 3

Views: 2501

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658092

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

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:

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:

Upvotes: 7

user330315
user330315

Reputation:

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.

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.

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. 

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: 

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

Upvotes: 3

Related Questions