Параметр функции anyelement, ошибка PostgreSQL?

Question

Я не вижу ошибки в этой реализации:

CREATE FUNCTION foo(anyelement) RETURNS SETOF int  AS $f$
    SELECT id FROM unnest(array[1,2,3]) t(id) 
    WHERE CASE WHEN (pg_typeof($1)::text)='integer' THEN $1::int>2 ELSE true END
$f$ LANGUAGE SQL IMMUTABLE;

SELECT * FROM foo(123); -- OK!
SELECT * FROM foo('test'::text); -- BUG

Это какая-то ошибка PostgreSQL или недокументированное ограничение anyelement тип данных?

---

Интересно: когда изолированы CASE пункт отлично работает:

 CREATE FUNCTION bar(anyelement) RETURNS boolean  AS $f$
   SELECT CASE WHEN (pg_typeof($1)::text)='integer' THEN $1::int>2;
 $f$ LANGUAGE SQL IMMUTABLE;

 SELECT bar('test'::text), bar(123), bar(1); -- works fine! 

Answer 1

Ваша проблема связана с тем, как планируются операторы SQL. SQL очень жестко относится к типам данных. Функции Postgres обеспечивают некоторую гибкость с полиморфным псевдотипом ANYELEMENT, но оператор SQL все еще планируется статически с данными типами.

Хотя выражение $1::int>2 никогда не выполняется, если $1 это не integer (таким образом можно избежать деления на ноль), это не спасет вас от синтаксической ошибки, возникающей на более ранней стадии планирования запроса.

Вы все еще можете сделать что-то с функцией, которая у вас есть. Используйте нетипизированный строковый литерал:

CREATE OR REPLACE FUNCTION foo(anyelement)
  RETURNS SETOF int AS
 $func$
   SELECT id FROM unnest(array[1,2,3]) id
   WHERE  CASE WHEN pg_typeof($1) = 'integer'::regtype
               THEN $1 > '2'  -- use a string literal!
               ELSE true END
$func$ LANGUAGE sql IMMUTABLE;

Это по крайней мере работает для всех символьных и числовых типов данных. Строковый литерал приводится к предоставленному типу данных. Но он все равно не будет работать для других типов данных, где "2" недопустимо.

Примечательно, что ваш второй пример не вызывает синтаксическую ошибку. Из моих тестов на Postgres 9.5 выяснилось, что синтаксическая ошибка вызывается, если функция не IMMUTABLE или для функций, возвращающих множество (RETURNS SETOF ... вместо RETURNS boolean) которые называются в FROM список: SELECT * FROM foo() вместо SELECT foo(), Казалось бы, планирование запросов обрабатывается по-разному для простого IMMUTABLE функции, которые могут быть встроены.

---

Помимо использования:

pg_typeof($1) = 'integer'::regtype

вместо:

(pg_typeof($1)::text)='integer'

Это вообще лучше. Всегда лучше приводить константу один раз вместо вычисленного значения каждый раз. И это работает для известных псевдонимов имени типа.

• Синтаксическая ошибка PostgreSQL в параметризованном запросе на "дату $1"

Answer 2

Это определенно связано с планировщиком / оптимизатором SQL. Поскольку функция объявлена ​​как IMMUTABLEоптимизатор пытается предварительно оценить части запроса. По какой-то причине он оценивает выражение $1::int>2 даже если вы вызываете функцию с text параметр.

Если вы измените свой foo функция к VOLATILE это будет работать нормально, потому что оптимизатор запросов не будет пытаться оптимизировать / предварительно оценить его.

Но почему bar Функция работает нормально, даже если это IMMUTABLE? Я предполагаю, что оптимизатор решает не предварительно оценивать его, поскольку он не использует выражения в циклах. Я имею в виду, что $1::int>2 оценивается только один раз, тогда как в foo Функция оценивается несколько раз.

---

Кажется, есть некоторые различия в том, как работает планировщик SQL SQL а также PLPGSQL язык. Та же функция в PLPGSQL работает отлично.

CREATE FUNCTION foo2(anyelement) RETURNS SETOF int AS $f$
DECLARE 
    i INTEGER;
BEGIN
    FOR i IN SELECT id FROM unnest(array[1,2,3]) t(id) 
        WHERE 
            CASE WHEN pg_typeof($1) = 'integer'::regtype 
                THEN $1::int > 2
                ELSE true END
    LOOP
        RETURN NEXT i;
    END LOOP;
END;
$f$ LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM foo2('test'::text); -- works fine