Преобразование запросов Oracle в PostgreSQL с помощью string_to_array()

У меня есть запрос ниже в 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

которые дают мне результат, например:

введите здесь описание изображения

Я хочу преобразовать запрос в Postgres. Я пробовал запрос типа:

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

Я также пробовал:

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

Но не получил правильного результата.
Я не знаю, как написать запрос, полностью эквивалентный версии Oracle. Любой?


postgresql sql oracle unnest set-returning-functions
person Dhaval Patel    schedule 13.02.2015    source источник

Ответы (2)


arrow_upward
3
arrow_downward

В выражении select a a — это не столбец, а имя псевдонима таблицы. Следовательно, это выражение выбирает полный набор строк (хотя и с одним столбцом), а не один столбец.

Вам необходимо определить правильные псевдонимы столбцов для производных таблиц. Также рекомендуется использовать функции, возвращающие множество, только в предложении from, а не в списке выбора.

Если вы не на 9.4, вам нужно сгенерировать «индекс», используя оконную функцию. Если вы на 9.4, то ответ Эрвина намного лучше.

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;

Альтернативным способом в 9.4 было бы использование опции with ordinality для создания индекса строки, если вам действительно нужно значение индекса:

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
person a_horse_with_no_name    schedule 13.02.2015

arrow_upward
7
arrow_downward

Начиная с Postgres 9.4 вы можете использовать unnest() с несколькими массивами, чтобы разделить их параллельно:

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

Это все. NULL значения заполняются автоматически для отсутствующих элементов справа.

Если параметры представлены как строки, преобразуйте их с помощью string_to_array() первым. Нравиться:

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

Подробнее и альтернативное решение для старых версий:

person Erwin Brandstetter    schedule 13.02.2015