Subject: ERROR: type of parameter 1 (fruit2) does not match that when preparing the plan (fruit1)

From: d <[email-masked]>
Date: Sat, 30 Apr 2022 16:23:19 -0700

Hi

This function should produce form_urlencoded data from a row.
It works on the first invoction.
but the second fails and references the table from the previous invocation.

In this case I am using pgbouncer but I have tested it without
and also without  the urldecode on several platforms (pg13)

thanks,
dh
-------to reproduce --------------------------------------
CREATE OR REPLACE FUNCTION record_to_form_data(p_r record)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
begin
return (
select string_agg(format('%s=%s',key,urlencode(value)),'&')
       from
	(select p_r.*) i,
	hstore(i.*) as h,each(h) );
       end;
$function$;

create table fruit1(id varchar not null,name varchar not null,color varchar);
create table fruit2(id varchar not null,name varchar not null);

insert into fruit1 values('1','apple','red');
insert into fruit2 values('1','apple');

select record_to_form_data(f.*) from fruit1 f;
select record_to_form_data(f.*) from fruit2 f;

--------------------------------
testit6=# select record_to_form_data(f.*) from fruit1 f;
    record_to_form_data    
---------------------------
 id=1&name=apple&color=red
(1 row)

testit6=# select record_to_form_data(f.*) from fruit2 f;
ERROR:  type of parameter 1 (fruit2) does not match that when preparing the plan (fruit1)
CONTEXT:  SQL statement "SELECT (
select string_agg(format('%s=%s',key,urlencode(value)),'&')
       from
	(select p_r.*) i,
	hstore(i.*) as h,each(h) )"
PL/pgSQL function record_to_form_data(record) line 6 at RETURN
testit6=# \c
psql (13.5 (Debian 13.5-0+deb11u1), server 13.6 (Debian 13.6-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "testit6" as user "david".
testit6=# select record_to_form_data(f.*) from fruit2 f;
 record_to_form_data 
---------------------
 id=1&name=apple
(1 row)

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 1 May 2022 08:58:38 -0700

>         hstore(i.*) as h,each(h) );

Not a bug, it is a documented limitation.

It is your use of "(select p_r.*)" that is problematic.

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

"""
The mutable nature of record variables presents another problem in this
connection. When fields of a record variable are used in expressions or
statements, the data types of the fields must not change from one call of
the function to the next, since each expression will be analyzed using the
data type that is present when the expression is first reached. EXECUTE can
be used to get around this problem when necessary.
"""

David J.