Postgres Type of Parameter does not match

I am having an odd problem with Postgres (10.5). I have a function, generate_unique_name which takes in three text values. It works fine; however, calling this function seems to be an issue. When I call the function using:

 SELECT generate_unique_name('basic', 'seeds', 'project=' || 2)

​x
 
 SELECT generate_unique_name('basic', 'seeds', 'project=' || 2)​

It works without issue. I can make the same call several times. Now, when I try the same call, but change the second parameter as below:

SELECT generate_unique_name('basic', 'queue', 'project=' || 2)

 
SELECT generate_unique_name('basic', 'queue', 'project=' || 2)​

Then it seems to fail with the error:

ERROR: type of parameter 9 (text) does not match that when preparing the plan (character varying) CONTEXT: PL/pgSQL function generate_unique_name(text,text,text) line 12 at assignment SQL state: 42804

I have tried changing the query to:

SELECT generate_unique_name('basic'::text, 'queue'::text, ('project=' || 2)::text)

 
SELECT generate_unique_name('basic'::text, 'queue'::text, ('project=' || 2)::text)​

But this also fails. If I then kill the connection to postgres DB, and create a new one, and instead start with the second query, it now works, but the first stops functioning.

It seems like postgres decides to stop treating the parameters as text part way through, for no apparent reason. Am I missing something?

EDIT: Code for generate_unique_name

CREATE OR REPLACE FUNCTION public.generate_unique_name(
    proposed_name text,
    table_name text,
    condition text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

DECLARE
    unique_name text;
    name_counter integer;
    r record;
    names_to_check text[];
BEGIN
    unique_name = proposed_name;
    name_counter = 0;

    FOR r IN EXECUTE 'SELECT name FROM ' || table_name || ' WHERE ' || condition LOOP
        names_to_check = array_append(names_to_check, r.name::text);
    END LOOP;

    WHILE unique_name = ANY(names_to_check) LOOP
        name_counter = name_counter + 1;
        unique_name = proposed_name || ' (' || name_counter || ')';
    END LOOP;
    RETURN unique_name;
END;

$BODY$;

 
CREATE OR REPLACE FUNCTION public.generate_unique_name(    proposed_name text,    table_name text,    condition text)    RETURNS text    LANGUAGE 'plpgsql'​    COST 100    VOLATILE AS $BODY$​DECLARE    unique_name text;    name_counter integer;    r record;    names_to_check text[];BEGIN    unique_name = proposed_name;    name_counter = 0;​    FOR r IN EXECUTE 'SELECT name FROM ' || table_name || ' WHERE ' || condition LOOP        names_to_check = array_append(names_to_check, r.name::text);    END LOOP;​    WHILE unique_name = ANY(names_to_check) LOOP        name_counter = name_counter + 1;        unique_name = proposed_name || ' (' || name_counter || ')';    END LOOP;    RETURN unique_name;END;​$BODY$;​

Answer

My guess is there’s a value in the name column of the queue table that causes an issue with

names_to_check = array_append(names_to_check, r.name::text)

 
names_to_check = array_append(names_to_check, r.name::text) ​

Advertisement

Answer