Обсуждение: Composite Types, arrays, and functions
I am getting an “invalid input syntax for type double precision” error when I return an array as part of a composite type from my C-language function. I can re-create this situation with just a type and a function.
My type is declared as follows:
CREATE TYPE my_grid AS (min_x double precision, min_y double precision, max_x double precision, max_y double precision, my_arr double precision[][]);
My function is defined as:
CREATE FUNCTION my_test()
RETURNS void AS $$
DECLARE
mine my_grid;
BEGIN
SELECT INTO mine (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double precision[][])::my_grid;
END
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public;
To get the error, I run the following query:
SELECT MY_TEST();
If I do a plain “SELECT (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double precision[][])::my_grid;” everything looks good.
Any help would be appreciated.
NOTICE: This message is covered by the Electronic Communications Privacy Act, Title 18, United States Code, Sections 2510-2521. This e-mail and any attached files are the exclusive property of Pictometry International Corp., are deemed privileged and confidential, and are intended solely for the use of the individual(s) or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or believe that you have received this message in error, please delete this e-mail and any attachments and notify the sender immediately. Any other use, re-creation, dissemination, forwarding or copying of this e-mail is strictly prohibited and may be unlawful.
On Mon, Jun 18, 2012 at 4:23 PM, Chris Schnaufer <chris.schnaufer@pictometry.com> wrote: > I am getting an “invalid input syntax for type double precision” error when > I return an array as part of a composite type from my C-language function. I > can re-create this situation with just a type and a function. > > > > My type is declared as follows: > > > > CREATE TYPE my_grid AS (min_x double precision, min_y double precision, > max_x double precision, max_y double precision, my_arr double > precision[][]); > > > > My function is defined as: > > > > CREATE FUNCTION my_test() > > RETURNS void AS $$ > > DECLARE > > mine my_grid; > > BEGIN > > > > SELECT INTO mine (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double > precision[][])::my_grid; > > END > > $$ > > LANGUAGE plpgsql > > SECURITY DEFINER > > SET search_path = public; > > > > > > To get the error, I run the following query: > > > > SELECT MY_TEST(); > > > > If I do a plain “SELECT (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double > precision[][])::my_grid;” everything looks good. > > > > Any help would be appreciated. This highly unfortunate behavior is coming form the fact that pl/pgsql assuming you want to pass a list of fields, not a constructed row type, when assigning with INTO. This would work: SELECT INTO mine ((0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double precision[][])::my_grid).*; so would this: mine := (0.1, 0.2, 0.3, 0.4,'{{88.9}}'::double precision[][])::my_grid; merlin