[SQL] Data into an array
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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data into an array
Hello 2008/8/26 sub3 <[EMAIL PROTECTED]>: > > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data into an array
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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data into an array
2008/8/27 sub3 <[EMAIL PROTECTED]>: > > 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
