Received: from falt.deep.ru (root@falt.deep.ru [195.91.137.199]) by hub.org (8.9.2/8.9.1) with ESMTP id JAA83458 for ; Fri, 19 Feb 1999 09:43:03 -0500 (EST) (envelope-from apikunov@falt.deep.ru) Received: from falt.deep.ru by falt.deep.ru (8.8.6/8.8.6) with SMTP id RAA12201 for ; Fri, 19 Feb 1999 17:36:37 +0300 Date: Fri, 19 Feb 1999 17:36:42 +0300 From: Alexey Pikunov X-Mailer: The Bat! (v1.15) S/N 540333A8 Reply-To: Alexey Pikunov Organization: DAFT MIPT X-Priority: 3 (Normal) Message-ID: <7733.990219@falt.deep.ru> To: pgsql-sql@postgreSQL.org Subject: [SQL] Troubles whith using aggregat inside function. Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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