Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: nehxby(at)gmail(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6529: Invalid numeric input syntax for 'select into' queries
Date: 2012-03-22 03:11:04
Message-ID: 16398.1332385864@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: mkkrishDate: 2012-03-22 06:00:34
Subject: BUG #6550: Querry failed while using throung php program.
Previous:From: Radosław SmoguraDate: 2012-03-21 21:10:48
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group