Преобразование запросов 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. Кто-нибудь?

Источник

2 ответа

Решение

В выражении 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
Источник

Начиная с 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);

Более подробная информация и альтернативное решение для более старых версий:

Источник
Другие вопросы по тегам