Using aggregate functions with SELECT INTO or FOR .. LOOP?

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