Thread: Using aggregate functions with SELECT INTO or FOR .. LOOP?
I would like to be able to use aggregate functions (e.g. sum(..))
in a pl/pgsql functions through SELECT INTO and FOR record IN SELECT ...
lines. The obvious (record.sum(..)) doesn't work when attempting to
recover the values. Is there a simple way of getting this information?
TIA!
-frank
You should alias the Aggregate function such as
CREATE OR REPLACE FUNCTION sp_test() RETURNS BOOLEAN AS '
DECLARE
rec RECORD;
BEGIN
SELECT SUM(a) AS asum,foo,bar INTO rec FROM test WHERE id = 1;
IF NOT FOUND THEN
RETURN FALSE;
END IF;
RAISE NOTICE ''%'',rec.asum;
RETURN TRUE;
END;' LANGUAGE 'plpgsql';
This should do what you wanted
HTH
On Thu, 24 Oct 2002, Frank Miles wrote:
> I would like to be able to use aggregate functions (e.g. sum(..))
> in a pl/pgsql functions through SELECT INTO and FOR record IN SELECT ...
> lines. The obvious (record.sum(..)) doesn't work when attempting to
> recover the values. Is there a simple way of getting this information?
>
> TIA!
>
> -frank
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Darren Ferguson
On Thu, 24 Oct 2002, Darren Ferguson wrote:
> You should alias the Aggregate function such as
>
> CREATE OR REPLACE FUNCTION sp_test() RETURNS BOOLEAN AS '
[snip]
Perfect, Darren! (he says sheepishly).
But for some reason postgres is doing something strange. Here's
a simple test case:
--
CREATE TABLE dummyTable (
id int,
qty int,
strng text
);
INSERT INTO dummyTable VALUES (1,3,'abc');
INSERT INTO dummyTable VALUES (1,7,'def');
INSERT INTO dummyTable VALUES (1,-2,'ghi');
INSERT INTO dummyTable VALUES (2,3,'Abc');
INSERT INTO dummyTable VALUES (2,7,'Def');
INSERT INTO dummyTable VALUES (2,-2,'Ghi');
INSERT INTO dummyTable VALUES (3,3,'abC');
INSERT INTO dummyTable VALUES (3,7,'deF');
INSERT INTO dummyTable VALUES (3,-2,'ghI');
CREATE OR REPLACE FUNCTION testF(int) RETURNS int AS '
DECLARE
xid ALIAS FOR $1;
rec record;
BEGIN
SELECT INTO rec sum(qty),id AS asum,id FROM dummyTable WHERE id= xid GROUP BY id;
IF NOT FOUND THEN
RETURN ''Nothing found'';
END IF;
RETURN rec.asum;
END;
' LANGUAGE 'plpgsql';
SELECT * FROM dummyTable WHERE id=2; -- correct rows
SELECT sum(qty) FROM dummyTable WHERE id=2; -- correct sum
SELECT testF(2); -- wrong sum
DROP TABLE dummyTable;
DROP FUNCTION testF(int);
--
The first select works properly, returns all appropriate rows,
The second returns the correct total (8).
The last (function call) returns 2 (!). The function in fact
appears to return the value of id, whatever that might be.
If the SELECT statement is reworded, eliminating the ",id" (twice):
SELECT INTO rec sum(qty) AS asum FROM dummyTable WHERE id= xid GROUP BY id;
then the correct total is returned. My guess is that the indexing on
the record gets 'off by 1' somehow.
I'm running Debian postgres (7.2.1-2; not sure what Oliver has
patched this to) on x86. Is this a known bug? Or am I doing something
characteristically stupid?
Thanks for all your help!
-frank
Frank Miles <fpm@u.washington.edu> writes:
> SELECT INTO rec sum(qty),id AS asum,id FROM dummyTable WHERE id= xid GROUP BY id;
^^^^^^^^^^
> RETURN rec.asum;
This is returning id.
regards, tom lane
It returns 2 because you are returning the xid in the function instead of the sum. You should give sum the alias not id. Then you should get the correct answer HTH On Fri, 25 Oct 2002, Frank Miles wrote: > On Thu, 24 Oct 2002, Darren Ferguson wrote: > > > You should alias the Aggregate function such as > > > > CREATE OR REPLACE FUNCTION sp_test() RETURNS BOOLEAN AS ' > > [snip] > > Perfect, Darren! (he says sheepishly). > > But for some reason postgres is doing something strange. Here's > a simple test case: > > -- > > CREATE TABLE dummyTable ( > id int, > qty int, > strng text > ); > INSERT INTO dummyTable VALUES (1,3,'abc'); > INSERT INTO dummyTable VALUES (1,7,'def'); > INSERT INTO dummyTable VALUES (1,-2,'ghi'); > INSERT INTO dummyTable VALUES (2,3,'Abc'); > INSERT INTO dummyTable VALUES (2,7,'Def'); > INSERT INTO dummyTable VALUES (2,-2,'Ghi'); > INSERT INTO dummyTable VALUES (3,3,'abC'); > INSERT INTO dummyTable VALUES (3,7,'deF'); > INSERT INTO dummyTable VALUES (3,-2,'ghI'); > > CREATE OR REPLACE FUNCTION testF(int) RETURNS int AS ' > DECLARE > xid ALIAS FOR $1; > rec record; > BEGIN > SELECT INTO rec sum(qty),id AS asum,id FROM dummyTable WHERE id= xid GROUP BY id; > IF NOT FOUND THEN > RETURN ''Nothing found''; > END IF; > RETURN rec.asum; > END; > ' LANGUAGE 'plpgsql'; > > SELECT * FROM dummyTable WHERE id=2; -- correct rows > SELECT sum(qty) FROM dummyTable WHERE id=2; -- correct sum > SELECT testF(2); -- wrong sum > > DROP TABLE dummyTable; > DROP FUNCTION testF(int); > > -- > > The first select works properly, returns all appropriate rows, > The second returns the correct total (8). > The last (function call) returns 2 (!). The function in fact > appears to return the value of id, whatever that might be. > > If the SELECT statement is reworded, eliminating the ",id" (twice): > SELECT INTO rec sum(qty) AS asum FROM dummyTable WHERE id= xid GROUP BY id; > then the correct total is returned. My guess is that the indexing on > the record gets 'off by 1' somehow. > > I'm running Debian postgres (7.2.1-2; not sure what Oliver has > patched this to) on x86. Is this a known bug? Or am I doing something > characteristically stupid? > > Thanks for all your help! > > -frank > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Darren Ferguson