BUG #6529: Invalid numeric input syntax for 'select into' queries

The following bug has been logged on the website:

Bug reference: 6529
Logged by: Maxim Turchin
Email address: nehxby(at)gmail(dot)com
PostgreSQL version: 9.1.3
Operating system: Ubuntu 11.10
Description:

CREATE TYPE test_bug_type AS
(value numeric(15,2),
currency_id varchar);

CREATE TABLE test_bug_table (
id serial PRIMARY KEY,
test_field test_bug_type
);

INSERT INTO test_bug_table (test_field) VALUES ('(15.50,USD)');

DO
$$DECLARE
test_bug_var test_bug_type;
BEGIN
SELECT test_field INTO test_bug_var FROM test_bug_table LIMIT 1;
--ERROR: invalid input syntax for type numeric: "(15.50,USD)"
--CONTEXT: PL/pgSQL function "inline_code_block" line 7 at SQL statement

-- OR:
INSERT INTO test_bug_table (test_field) VALUES ('(35.80,EUR)')
RETURNING test_field INTO test_bug_var;
--ERROR: invalid input syntax for type numeric: "(35.80,EUR)"

RAISE NOTICE 'Test %', test_bug_var;
END;$$;

DROP TABLE test_bug_table;
DROP TYPE test_bug_type;

nehxby(at)gmail(dot)com writes:
> [ test_bug_type is composite ]
> $$DECLARE
> test_bug_var test_bug_type;
> BEGIN
> SELECT test_field INTO test_bug_var FROM test_bug_table LIMIT 1;
> --ERROR: invalid input syntax for type numeric: "(15.50,USD)"
> --CONTEXT: PL/pgSQL function "inline_code_block" line 7 at SQL statement
>
> -- OR:
> INSERT INTO test_bug_table (test_field) VALUES ('(35.80,EUR)')
> RETURNING test_field INTO test_bug_var;
> --ERROR: invalid input syntax for type numeric: "(35.80,EUR)"
>
> RAISE NOTICE 'Test %', test_bug_var;
> END;$$;

plpgsql is expecting the SELECT or RETURNING list to supply the
components of the composite target variable separately. That's
sometimes convenient and sometimes not so much, but I'm not sure we
could change it without breaking an awful lot of peoples' functions.

The way to deal with this when your source value is a composite column
is to expand the source value into separate components, for instance

SELECT (test_field).* INTO test_bug_var FROM test_bug_table LIMIT 1;

INSERT INTO test_bug_table (test_field) VALUES ('(35.80,EUR)')
RETURNING (test_field).* INTO test_bug_var;

(The parentheses are necessary here because without them "test_field"
would be syntactically a table name, not a column name.)

regards, tom lane