Postgres Type of Parameter does not match

Question

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

You might need to share the code of function generate_unique_name — GMB
– GMB, Commented Nov 30, 2018 at 19:20
I removed some values from the queue table, and it did start working. So that does appear to be the issue. Thank you. :) The values had brackets and underscores in them. Not quite sure how to fix that though. I would assume that when it's being appended to the array, it would still be 'sql safe', seeing as its pulled from an sql table. — ChrisMM
– ChrisMM, Commented Nov 30, 2018 at 19:52

Joe Stefanelli · Accepted Answer · 2018-11-30 19:53:08Z

1

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)

answered Nov 30, 2018 at 19:53

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChrisMM Over a year ago

I removed some values from the queue table, and it did start working. So that does appear to be the issue. Thank you. :) The values had brackets and underscores in them. Not quite sure how to fix that though. I would assume that when it's being appended to the array, it would still be 'sql safe', seeing as its pulled from an sql table.

ChrisMM · Accepted Answer · 2018-12-04 15:32:58Z

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;

Collectives™ on Stack Overflow

Postgres Type of Parameter does not match

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related