Re: Composite Types, arrays, and functions

On Mon, Jun 18, 2012 at 4:23 PM, Chris Schnaufer
<chris(dot)schnaufer(at)pictometry(dot)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