PL/PGSQL bug in handling composite types - Mailing list pgsql-hackers

Hi,

we have found a way to make pl/pgsql throw an error for
a legitimate use case that works in plain SQL.

Minimal test case:

create table x1 (id serial primary key, d timestamptz);
create table x2 (id serial primary key, d timestamptz);
insert into x2 (d) values ('now');
create type mytype as (id bigint, d timestamptz);

Casting a set of values to "mytype" works in SQL:

=# select (max(id),min(d))::mytype from x2;                 row
------------------------------------- (1,"2012-04-24 09:04:07.475315+02")
(1 row)

=# select (max(id),min(d))::mytype from x1; row
----- (,)
(1 row)

Even empty data in plain text casted to "mytype" works
and individual fields can be tested for IS NULL:

=# select '(,)'::mytype; mytype
-------- (,)
(1 row)

=# select ('(,)'::mytype).id is null; ?column?
---------- t
(1 row)

Now, try this from PL/PGSQL:

create or replace function testfunc1() returns mytype as $$declare  v_sql text;  mt mytype;
begin  v_sql := 'select (max(id),min(d))::mytype from x1';  execute v_sql into mt;  return mt;
end;$$ language plpgsql;

=# select testfunc1();
ERROR:  invalid input syntax for integer: "(,)"
CONTEXT:  PL/pgSQL function "testfunc1" line 6 at EXECUTE statement

The same error happens with table "x2" with data in it:

create or replace function testfunc2() returns mytype as $$declare  v_sql text;  mt mytype;
begin  v_sql := 'select (max(id),min(d))::mytype from x2';  execute v_sql into mt;  return mt;
end;$$ language plpgsql;

=# select testfunc2();
ERROR:  invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT:  PL/pgSQL function "testfunc2" line 6 at EXECUTE statement

Same happens also with non-dynamic queries:

create or replace function testfunc1a() returns mytype as $$declare  mt mytype;
begin  select (max(id),min(d))::mytype into mt from x1;  return mt;
end;$$ language plpgsql;

=# select testfunc1a();
ERROR:  invalid input syntax for integer: "(,)"
CONTEXT:  PL/pgSQL function "testfunc1a" line 4 at SQL statement

create or replace function testfunc2a() returns mytype as $$declare  mt mytype;
begin  select (max(id),min(d))::mytype into mt from x2;  return mt;
end;$$ language plpgsql;

=# select testfunc2a();
ERROR:  invalid input syntax for integer: "(1,"2012-04-24 09:04:07.475315+02")"
CONTEXT:  PL/pgSQL function "testfunc2a" line 4 at SQL statement

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig&  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de     http://www.postgresql.at/