Re: Using aggregate functions .. (a BUG?) - Mailing list pgsql-general

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