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
