From: | Boszormenyi Zoltan <zb(at)cybertec(dot)at> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | hs(at)cybertec(dot)at, michael(dot)krueger(at)voipfuture(dot)com |
Subject: | PL/PGSQL bug in handling composite types |
Date: | 2012-04-24 07:59:18 |
Message-ID: | 4F965D56.3010006@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Boszormenyi Zoltan | 2012-04-24 08:01:58 | Re: PL/PGSQL bug in handling composite types |
Previous Message | Sandro Santilli | 2012-04-24 07:31:36 | Re: Gsoc2012 idea, tablesample |