Thread: Error in PLpgSQL with SELECT INTO and composite types

Tested on 8.2, 8.3 and CVS HEAD:

CREATE TYPE atype AS (a int);
CREATE TYPE btype AS (compcol atype);

CREATE FUNCTION foofunc() RETURNS void AS $$
  declare
   avar atype;
   bvar btype;
begin
   SELECT '("(1)")'::btype INTO bvar;  -- Should work, but errors
end;
$$ LANGUAGE plpgsql;

SELECT foofunc();

Gives error:

ERROR:  invalid input syntax for integer: "(1)"
CONTEXT:  PL/pgSQL function "foofunc" line 6 at SQL statement

Surely assigning a value of btype to a variable of the same type should
always work..

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>    SELECT '("(1)")'::btype INTO bvar;  -- Should work, but errors

No, because it's expecting the SELECT to yield a separate column for
each column of the composite variable bvar.  Something like

SELECT ('("(1)")'::btype).* INTO bvar;

would be correct.  To make it work the way you're thinking would break
a lot of code that expects the existing semantics --- instead of

select expr1, expr2 into two_column_record_var from ...

people would have to write something like

select row(expr1, expr2) into two_column_record_var from ...

            regards, tom lane