From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Chris Schnaufer <chris(dot)schnaufer(at)pictometry(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Composite Types, arrays, and functions |
Date: | 2012-06-18 22:41:47 |
Message-ID: | CAHyXU0yigkOhCzCqu2=NEn+FMHJwsAkH0d9kJOG3iU7FT3BfCQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-06-19 02:32:46 | Feature discussion: Should syntax errors abort a transaction? |
Previous Message | Paul Jones | 2012-06-18 21:49:32 | Fine-grained replication? |