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

2012-04-24 09:59 keltezéssel, Boszormenyi Zoltan írta:
> 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
>

Sorry, I didn't mention the version tested: 9.0.6 and 9.1.3.

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/