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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers