Function parameter anyelement, PostgreSQL bug?

Question

I do not see the bug in this implementation:

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

Is this some kind of PostgreSQL bug or a non-documented restriction on anyelement datatype?

---

Interesting: when isolated the CASE clause works fine:

 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

It's definitely related to SQL planner/optimizer. Since the function is declared as IMMUTABLE, the optimizer tries to pre-evaluate the query parts. For some reason, it evaluates the expression $1::int>2 even if you call the function with text parameter.

If you change your foo function to VOLATILE it will work fine, because the query optimizer will not try to optimize/pre-evalute it.

But why bar function works fine even if it's IMMUTABLE? I guess the optimizer decides not to pre-evalute it as it does not use expressions in loops. What I mean is that $1::int>2 is evaluated only once, whereas in foo function it's evaluated multiple times.

---

Seems like there are some differences how SQL planner works for SQL and PLPGSQL language. The same function in PLPGSQL works fine.

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

Answer 2

Your problem is due to how SQL statements are planned. SQL is very rigid about data types. Postgres functions provide some flexibility with the polymorphic pseudo-type ANYELEMENT, but the SQL statement is still planned with the given types statically.

While the expression $1::int>2 is never executed if $1 is not an integer (you could avoid division by zero this way), this cannot save you from the syntax error that arises at the earlier stage of planning the query.

You can still do something with the function you have. Use an untyped string literal:

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;

This at least works for all character and numeric data types. The string literal is coerced to the provided data type. But it will still fail for other data types where '2' is not valid.

It's remarkable that your second example does not trigger the syntax error. It emerged from my tests on Postgres 9.5 that the syntax error is triggered if the function is not IMMUTABLE or for set-returning functions (RETURNS SETOF ... instead of RETURNS boolean) that are called in the FROM list: SELECT * FROM foo() instead of SELECT foo(). It would seem that query planning is handled differently for simple IMMUTABLE functions which can be inlined.

---

Aside, use:

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

instead of:

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

That's generally better. It's always better to cast the constant once instead of the computed value every time. And this works for known aliases of the type name as well.

• PostgreSQL syntax error in parameterized query on "date $1"