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

nehxby@gmail.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