Как избежать множественных функций с помощью синтаксиса (func()). * В SQL-запросе?

Контекст

Когда функция возвращает TABLE или SETOF composite-type, как эта функция-образец:

CREATE FUNCTION func(n int) returns table(i int, j bigint) as $$
BEGIN
  RETURN QUERY select 1,n::bigint 
      union all select 2,n*n::bigint
      union all select 3,n*n*n::bigint;
END
$$ language plpgsql;

к результатам можно обращаться различными способами:

1) select * from func(3) создаст эти выходные столбцы:

 i | j 
---+---
 1 |  3
 2 |  9
 3 | 27

2) select func(3) будет выдавать только один выходной столбец типа ROW.

 func  
-------
 (1,3)
 (2,9)
 (3,27)

3) select (func(3)).* будет производить как # 1:

 i | j 
---+---
 1 |  3
 2 |  9
 3 | 27

Когда аргумент функции приходит из таблицы или подзапроса, синтаксиС# 3 является единственным возможным, как в:

select N, (func(N)).* from (select 2 as N union select 3 as N) s;

или как в этом связанном ответе. Если бы мы имели LATERAL JOIN, мы могли бы использовать это, но до тех пор, пока PostgreSQL 9.3 не будет, он не будет поддерживаться, а предыдущие версии все равно будут использоваться в течение многих лет.

Проблема

Теперь проблема с синтаксисом № 3 заключается в том, что функция вызывается столько раз, сколько в ней есть столбцы. Нет никаких очевидных причин для этого, но это происходит. Мы можем увидеть это в версии 9.2, добавив в функцию RAISE NOTICE 'called for %', n. С запросом выше он выводит:

NOTICE:  called for 2
NOTICE:  called for 2
NOTICE:  called for 3
NOTICE:  called for 3

Теперь, если функция изменена, чтобы возвращать 4 столбца, например:

CREATE FUNCTION func(n int) returns table(i int, j bigint,k int, l int) as $$
BEGIN
  raise notice 'called for %', n;
  RETURN QUERY select 1,n::bigint,1,1 
      union all select 2,n*n::bigint,1,1
      union all select 3,n*n*n::bigint,1,1;
END                                        
$$ language plpgsql stable;

то те же выходные запросы:

NOTICE:  called for 2
NOTICE:  called for 2
NOTICE:  called for 2
NOTICE:  called for 2
NOTICE:  called for 3
NOTICE:  called for 3
NOTICE:  called for 3
NOTICE:  called for 3

потребовалось 2 вызова функций, 8 фактически были выполнены. Отношение - это количество выходных столбцов.

С синтаксисом # 2, который производит тот же результат, за исключением макета выходных колонок, этих множественных вызовов не происходит:

select N,func(N) from (select 2 as N union select 3 as N) s;

дает:

NOTICE:  called for 2
NOTICE:  called for 3

за которым следуют 6 результирующих строк:

 n |    func    
---+------------
 2 | (1,2,1,1)
 2 | (2,4,1,1)
 2 | (3,8,1,1)
 3 | (1,3,1,1)
 3 | (2,9,1,1)
 3 | (3,27,1,1)

Вопросы

Есть ли синтаксис или конструктор с 9.2, который достиг бы ожидаемого результата, выполняя только минимально требуемые вызовы функций?

Бонусный вопрос: почему выполняются множественные оценки?

Ответы

Ответ 1

Вы можете обернуть его в подзапрос, но не гарантированный сейф без взлома OFFSET 0. В 9.3 используйте LATERAL. Проблема связана с тем, что парсер эффективно макрорасширяет * в список столбцов.

Обход

Где:

SELECT (my_func(x)).* FROM some_table;

будет оценивать my_func n раз за n столбцы результата от функции, эта формулировка:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table
) sub;

обычно не будет и не будет добавлять дополнительное сканирование во время выполнения. Чтобы гарантировать, что множественная оценка не будет выполнена, вы можете использовать OFFSET 0 взломать или злоупотреблять PostgreSQL, чтобы оптимизировать границы CTE:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

или

WITH tmp(mf) AS (
    SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;

В PostgreSQL 9.3 вы можете использовать LATERAL, чтобы получить более здравое поведение:

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;

LEFT JOIN LATERAL ... ON true сохраняет все строки, такие как исходный запрос, даже если вызов функции не возвращает строку.

Demo

Создайте функцию, которая не является встроенной в качестве демонстрации:

CREATE OR REPLACE FUNCTION my_func(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
BEGIN
    RAISE NOTICE 'my_func(%)',$1;
    RETURN QUERY SELECT $1, $1, $1;
END;
$$ LANGUAGE plpgsql;

и таблицу фиктивных данных:

CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x;

затем попробуйте приведенные выше версии. Вы увидите, что первый вызывает три уведомления за вызов; последний только поднимает один.

Почему?

Хороший вопрос. Это ужасно.

Похоже:

(func(x)).*

расширяется как:

(my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l

при разборе, согласно взглядам debug_print_parse, debug_print_rewritten и debug_print_plan. Дерево разбора (обрезанное) выглядит следующим образом:

   :targetList (
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 1 
         :resulttype 23 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 1 
      :resname i 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 2 
         :resulttype 20 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 2 
      :resname j 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 3 
         :...
         }
      :resno 3 
      :resname k 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 4 
          ...
         }
      :resno 4 
      :resname l 
       ...
      }
   )

Итак, в основном, мы используем немой синтаксический анализатор для расширения подстановочных знаков путем клонирования узлов.