Thread: Bug #724: Problmes creating aggregate functions in 7.2.1

Dan Graham (graham@molbio.uoregon.edu) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Problmes creating aggregate functions in 7.2.1

Long Description
I've included a short sample which shows what I'm having problems with.

I using version 7.2.1 on Slackware 7.  

Basically, I can't figure out the rules for creating aggregate functions.

I don't know if the bug is in
1) Postgres
2) Postgres docs
3) The space between my ears.

I have the following code in a file, testfunc.sql, which 
I invoke with psql -a -e -f testfunc.sql 

I get two kinds of aggregate function 'errors'.  
One of the aggregate functions just can't be created, the error says
the sfunc parameter I'm  using can't be found, but I just used the
same sfunc name a few lines above.

The the second 'error' is that the return from 'aggmytextcat' is 
blank, not what I expected.


I'm sorry if this has been reported and I missed it.  I've been 
lurking on the mailing lists for a long time, but not everything
I've seen has sunk in.


Dan



Sample Code


create table testme_dan (fld1 text, fld2 text, fld3 int4, fld4 int4);
insert into testme_dan Values ('hello', 'there',5,10);
insert into testme_dan Values ('goodbye', 'now',12,25);

-- The table is now made and filled
-- Now create some functions using internal functions

create function mytextcat(text,text) returns text 
as 'select textcat($1,$2)::text;'
language SQL
;

create function myint4_sum(int4,int4) returns int4
as 'select int4_sum($1,$2)::int4;'
language SQL
;

-- Test them

select textcat(fld1,fld2) from testme_dan;
select mytextcat(fld1,fld2) from testme_dan;
select int4_sum(fld3,fld4) from testme_dan;
select myint4_sum(fld3,fld4) from testme_dan;

-- now create some aggregate functions, whose only apparent difference
-- is that some call internal functions and others call the just defined
-- and tested SQL functions. Yet the 2 of the 4 aggregates fail, at different
-- places.

create aggregate aggcat (basetype=text, sfunc=textcat, stype=text);
create aggregate aggmycat (basetype=text, sfunc=mytextcat, stype=text);

create aggregate aggint4sum (basetype = int4, sfunc = int4_sum, stype = int4);
create aggregate aggmyint4sum (basetype = int4, sfunc = myint4_sum, stype = int4);


-- Now test the aggregates 

select aggcat(fld1) from testme_dan;
select aggmycat(fld1) from testme_dan;

select aggint4sum(fld3) from testme_dan;
select aggmyint4sum(fld3) from testme_dan;


-- cleanup
 drop aggregate aggcat(text);
 drop aggregate aggmycat(text);
 drop aggregate aggint4sum(int4);
 drop aggregate aggmyint4sum(int4);
 drop function myint4_sum(int4,int4);
 drop function mytextcat(text,text);
 drop table testme_dan;



No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> create aggregate aggint4sum (basetype = int4, sfunc = int4_sum, stype = int4);

This doesn't work because there's no int4_sum(int4, int4).

regression=# \df int4_sum
                 List of functions
 Result data type |   Name   | Argument data types
------------------+----------+---------------------
 bigint           | int4_sum | bigint, integer
                               ^^^^^^
(1 row)

You don't get any automatic conversion when defining an aggregate: the
datatypes have to match exactly.

Your other three examples work for me.

            regards, tom lane