Thread: Composite Types, arrays, and functions

Composite Types, arrays, and functions

From

Chris Schnaufer

Date:

18 June 2012, 21:23:45

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.

Re: Composite Types, arrays, and functions

From

Merlin Moncure

Date:

18 June 2012, 22:42:04

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