Postgres Pro
Downloads
Home   >   mailing lists

Thread: Data into an array

Data into an array

From
sub3
Date:
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);END LOOP;
 
END;
$$ LANGUAGE plpgsql;
-- 
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.

Re: Data into an array

From
"Pavel Stehule"
Date:
Hello

2008/8/26 sub3 <steve@subwest.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: Data into an array

From
sub3
Date:
Thanks, I got it to work.

But now I have a different problem.
If I execute: select * from getvss(1, now())
it works. But instead, if I execute it as: select *, getvss(d1.id, now()) from d1

I don't get separate columns for the data coming from getvss, it is all in
one column, unlike the previous select.  Is there anyway to force that into
different columns?
Thanks.


CREATE or REPLACE FUNCTION getvss(f_pvid integer, f_time timestamp without
time zone, OUT o_port integer[], OUT o_conf double precision[], OUT d_port
integer[], OUT d_conf double precision[])as $$ DECLARE vssview RECORD;
BEGIN 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   o_port := array_append(o_port, vssview.portid);   o_conf := 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   d_port := array_append(d_port, vssview.portid);   d_conf := array_append(d_conf,
vssview.confidence);END LOOP;
 
END;
$$ LANGUAGE plpgsql;

-- 
View this message in context: http://www.nabble.com/Data-into-an-array-tp19167834p19172916.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: Data into an array

From
"Pavel Stehule"
Date:
2008/8/27 sub3 <steve@subwest.com>:
>
> Thanks, I got it to work.
>
> But now I have a different problem.
> If I execute:
>  select * from getvss(1, now())
> it works. But instead, if I execute it as:
>  select *, getvss(d1.id, now()) from d1

try to
select (getvss(d1.id, now()).* from d1

regards
Pavel Stehule

>
> I don't get separate columns for the data coming from getvss, it is all in
> one column, unlike the previous select.  Is there anyway to force that into
> different columns?
> Thanks.
>
>
> CREATE or REPLACE FUNCTION getvss(f_pvid integer, f_time timestamp without
> time zone, OUT o_port integer[], OUT o_conf double precision[], OUT d_port
> integer[], OUT d_conf double precision[])
>  as $$ DECLARE
>  vssview RECORD;
> BEGIN
>  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
>    o_port := array_append(o_port, vssview.portid);
>    o_conf := 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
>    d_port := array_append(d_port, vssview.portid);
>    d_conf := array_append(d_conf, vssview.confidence);
>  END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
> --
> View this message in context: http://www.nabble.com/Data-into-an-array-tp19167834p19172916.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>