Polymorphic type inference failure for anyelement in parameterized queries

[qlina911-commits]

If you create an SQL script with the following content:

\set ON_ERROR_STOP on

CREATE OR REPLACE FUNCTION catalog.compare2(
    current_type attribute_t,
    current_value text,
    compared_value anyelement
)
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Hello';
    RETURN TRUE;
END;
$$;

SELECT catalog.compare2('bool', 'true', TRUE);

It will execute successfully with no errors.

However, if you attempt to execute a similar query from Dart:

response = await c.execute(
    Sql.named("SELECT catalog.compare2('bool', 'true', @x1)"),
    parameters: {'x1': true},
  );

An error will occur:

Unhandled exception:
Severity.error 42804: could not determine polymorphic type because input has type unknown
#0      _PgSessionBase._prepare (package:postgres/src/v3/connection.dart:199:35)
#1      _PgSessionBase.execute (package:postgres/src/v3/connection.dart:183:30)
#2      main (package:qqexample/qqexample.dart:16:22)
<asynchronous suspension>

The error can be fixed by explicitly casting the type in the query, but since the type is unknown (anyelement), it cannot be done.

How can this strange behavior of the postgres package be resolved?

[isoos]

@qlina911-commits: Do you know the type in Dart upfront? In that case you could use TypedValue or chain Type.<something> with the .value(x) method: https://pub.dev/documentation/postgres/latest/postgres/Type/value.html and use that in the query.

Curious: what's the role of such function in your app?