Thread: [SQL] Troubles whith using aggregat inside function.

[SQL] Troubles whith using aggregat inside function.

From
Alexey Pikunov
Date:
Hello !

I have troble creating function:

/************************ Begin *****************************/
mgetty=> create function get_stat(datetime, datetime) returns timespan as'
mgetty'> select
mgetty'> sum(tt1.stop - tt1.start)+($2 - $1)+(tt3.stop - $1)+($2 - tt4.start)
mgetty'> from ttyd0 tt1,ttyd0 tt2,ttyd0 tt3,ttyd0 tt4
mgetty'> where tt1.start > $1 and tt1.stop < $2
mgetty'> and   tt2.start < $1 and tt2.stop > $2
mgetty'> and   tt3.start < $1 and tt3.stop < $2 and tt3.stop > $1
mgetty'> and   tt4.start > $1 and tt4.stop > $2 and tt4.start < $2;
mgetty'> ' language 'sql';
ERROR:  parser: illegal use of aggregates or non-group column in target list
mgetty=> \d ttyd0

Table    = ttyd0
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| start                            | datetime not null                |     8 |
| stop                             | datetime not null                |     8 |
| boundrate                        | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
Index:    ttyd0_start_key
/************************* End ******************************/

In addition, when I try to get sum() for empty dataset Server cancels
client (psql).
Is there any solution for this problems?
(PostgreeSQL v6.4.2)

Best regards,
 Alexey                          mailto:apikunov@falt.deep.ru