Question 189230 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres `anyelement` function variable gives error: could not determine polymorphic type because input has type unknown

I have the following function which I want to pass anyelement and it should return whether it is a valid URL by matching the regex:

CREATE OR REPLACE FUNCTION isURL(data anyelement)
  RETURNS BOOLEAN
AS $$
BEGIN
  RETURN data::text ~ '^https?://[-a-zA-Z0-9@:%._+~#=]{2,255}.[a-z]{2,6}(/[-a-zA-Z0-9@:%._+~#=]*)*(?[-a-zA-Z0-9@:%_+.~#()?&//=]*)?$';
END
$$ LANGUAGE plpgsql;

When I run the following:

select isurl('https://example.com/submit');

it gives error:

ERROR:  could not determine polymorphic type because input has type unknown

If I cast the input data to ::text then it works:

select isurl('https://example.com/submit'::text);

However, this defeats the purpose of my anyelement type variable.

What does the error mean? Is there a way to pass something without casting? I don’t mind things happening inside the function itself, as long as I am not having to cast stuff before passing as variable.

Tags:

2

Answers


  1. 0

    While it can be any (scalar) type it has to be a definite type. That means you need to be clear about what type a quoted literal is.

    What type is this '2021-03-31'? Did you say "text", no sorry, it’s a date. If you said "date", no sorry it’s a part-number stored as text.

    "That doesn’t matter, I just want the text representation" you say. But I’m afraid it very much does matter.

    testdb=> SELECT (date '31/03/2023')::text, (boolean 't')::text;
┌────────────┬──────┐
│    text    │ text │
├────────────┼──────┤
│ 2023-03-31 │ true │
└────────────┴──────┘
(1 row)

    The input representation of a typed value is not the same as the standard type representation of it. So whether your "thing in quotes" is text or date or boolean makes a real difference.

    This problem only exists for literals, and normally the parser can figure it out based on context. If you are saving that value into a date column it will decide the quoted literal of unknown type must be a date. Your function explicitly doesn’t supply any context though, so the parser has nothing to work with.

    So for anything other than literals (columns, expressions, parameterised query values) you should be fine (although you might not be testing what you think you are).

    testdb=> CREATE TABLE t1 (i int, b bool, d date, t text);
CREATE TABLE
testdb=> INSERT INTO t1 VALUES (001, true, '31/03/2023-03', 'Hello');
INSERT 0 1
testdb=> CREATE OR REPLACE FUNCTION stringlength(e anyelement) RETURNS int LANGUAGE sql AS $$SELECT length(e::text);$$;
CREATE FUNCTION
testdb=> SELECT i, stringlength(i), b, stringlength(b), d, stringlength(d), t, stringlength(t) FROM t1;
┌───┬──────────────┬───┬──────────────┬────────────┬──────────────┬───────┬──────────────┐
│ i │ stringlength │ b │ stringlength │     d      │ stringlength │   t   │ stringlength │
├───┼──────────────┼───┼──────────────┼────────────┼──────────────┼───────┼──────────────┤
│ 1 │            1 │ t │            4 │ 2023-03-31 │           10 │ Hello │            5 │
└───┴──────────────┴───┴──────────────┴────────────┴──────────────┴───────┴──────────────┘
    Login or Signup to reply.
  2. 0

    With respect I think the problem is actually here: "However, this defeats the purpose of my anyelement type variable."

    Postgres’s polymorphism is a bit limited so you may be expecting it to be useful in a way it just isn’t.

    The reason to use anyelement in postgres is if you’re going to return it or a polymorphic container like arrays or ranges. It doesn’t actually instantiate a new version of the function or delay operator or function lookup inside the function definition.

    It actually looks in your example that you really do want to declare your parameter as text. Then it’s up to postgres’s implicit casting to decide whether other types can be passed without explicit casts — which is generally what you want.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search