CODEWITHSUNDEEP
sqlpostgresqlpostgresql-10
ChrisMM
ChrisMM

Reputation: 10106

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)

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)

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)

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$;

Upvotes: 1

Views: 2872

Answers (2)

ChrisMM
ChrisMM

Reputation: 10106

As Joe mentioned, the issue was with the array_append, which I could not figure out a way to fix. Instead, the generate_unique_names functions was changed to just query the DB continuously.

CREATE OR REPLACE FUNCTION generate_unique_name (proposed_name text, table_name text, condition text) RETURNS text AS
$BODY$
DECLARE
    unique_name text;
    name_counter integer;
    not_unique boolean;
BEGIN
    unique_name = proposed_name;
    name_counter = 0;

    EXECUTE 'SELECT COUNT(*)!=0 FROM ' || table_name || ' WHERE ' || condition || ' AND name = ''' || unique_name || '''' INTO not_unique;

    WHILE not_unique LOOP
        name_counter = name_counter + 1;
        unique_name = proposed_name || ' (' || name_counter || ')';
        EXECUTE 'SELECT COUNT(*)!=0 FROM ' || table_name || ' WHERE ' || condition || ' AND name = ''' || unique_name || '''' INTO not_unique;
    END LOOP;
    RETURN unique_name;
END;
$BODY$ LANGUAGE plpgsql;

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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)

Upvotes: 1

Related Questions