Thread: PL/PGSQL bug in handling composite types
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/
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/
2012-04-24 10:01 keltezéssel, Boszormenyi Zoltan írta:
> 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.
The same also happens with current 9.2 GIT.
>
> 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/
Boszormenyi Zoltan <zb@cybertec.at> writes:
> we have found a way to make pl/pgsql throw an error for
> a legitimate use case that works in plain SQL.
The fact that it doesn't work the way you thought doesn't make it a bug.
plpgsql expects an assignment "INTO row_variable" to be supplied from
one select column per field in the composite variable. So instead of
executing 'select (max(id),min(d))::mytype from x1', try executing
'select max(id),min(d) from x1'.
If we were to change this, we would break a lot of existing plpgsql
code; or, if we tried to support both usages, we would create nasty
semantic ambiguities for single-column composites. Possibly the
documentation in "39.5.3. Executing a Query with a Single-row Result"
could be improved though. To my eyes, it does say that this is what
happens, but it doesn't really emphasize the point that the SELECT's
output has to be "exploded" not delivered as a single composite column.
regards, tom lane