Re: Data into an array

Hello

2008/8/26 sub3 <steve(at)subwest(dot)com>:
>
> Hi,
> I am trying to compress some data down into a single row (making it an
> array). I believe I am close, but I still keep getting an error: "ERROR:
> query has no destination for result data"
>
> Does anyone have an idea what is going wrong? Thanks.
>
> CREATE or REPLACE FUNCTION getVSS(f_pvid integer, f_time timestamp, OUT
> o_port integer[], OUT o_conf double precision[], OUT d_port integer[], OUT
> d_conf double precision[]) RETURNS setof record
> as $$ DECLARE
> vssview RECORD;
> BEGIN
> o_port = ARRAY[0];
> o_conf = ARRAY[0];
> d_port = ARRAY[0];
> d_conf = ARRAY[0];
> FOR vssview IN select vss_orig.portid, vss_orig.confidence from vss,
> vss_orig where vss.vssmsg = vss_orig.vssmsg AND pvid = f_pvid and f_time
> between starttime and endtime LOOP
> select array_append(o_port, vssview.portid);
> select array_append(o_conf, vssview.confidence);
> END LOOP;
> FOR vssview IN select vss_dest.portid, vss_dest.confidence from vss,
> vss_dest where vss.vssmsg = vss_dest.vssmsg AND pvid = f_pvid and f_time
> between starttime and endtime LOOP

> select array_append(d_port, vssview.portid);
> select array_append(d_conf, vssview.confidence);

you cannot use free select inside function. Use SELECT INTO or just
assign statement:

correct:

d_port := array_append(d_port, vssview.portid);
d_conf := array_append(d_conf, vssview.confidence);

1. array_append is function! 2. PostgreSQL function never use byref variables.

> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
> --

Regards
Pavel Stehule

> View this message in context: http://www.nabble.com/Data-into-an-array-tp19167834p19167834.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>