Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 09E5E640430 for ; Fri, 30 Oct 2009 16:29:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 81500-07 for ; Fri, 30 Oct 2009 19:29:22 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f211.google.com (mail-gx0-f211.google.com [209.85.217.211]) by mail.postgresql.org (Postfix) with ESMTP id C7611640415 for ; Fri, 30 Oct 2009 16:29:25 -0300 (ADT) Received: by gxk3 with SMTP id 3so3996073gxk.15 for ; Fri, 30 Oct 2009 12:29:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type; bh=sGiuu4JuWXA0F9wAk+PiW4o/CdE7BeC9161Qd4XTkhY=; b=FfpMUBffgZENqe+XUBFs48USnFS9jcKt69v/ERi5Y6dsXnyjNUBjmGysfwEMqM3cqt SlyCzum/70P72iMf7Fy5UnLj9dMX0iUBvzH/oRpxshU123JMBwlVvqPuydXDiJz8OjnG 8Kw20voCSD1ivi5knYOc8XStC/56K8mh1ToDI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=lmyKa5sg+Y7xu9agpDO/uxHn8+yRS9uCUj5uZgDWPRWQaj3BL0WB4f5KYYQJODKHsk dVGydRcl0K20h7PPK45mszCggv/YzYQeji9tEexw5NWaIOLEw0U95rzrVPnqJlTUpkcb ZmfNPXdHTrXUPpqVsgvFZOozJ34LoeyySnp1g= MIME-Version: 1.0 Received: by 10.150.48.27 with SMTP id v27mr3655480ybv.291.1256930963904; Fri, 30 Oct 2009 12:29:23 -0700 (PDT) In-Reply-To: References: Date: Fri, 30 Oct 2009 21:29:23 +0200 X-Google-Sender-Auth: 04c9e96ccf885460 Message-ID: <5a9699850910301229r6e3b936g7c86d2ab0cdaa6c6@mail.gmail.com> Subject: Re: Function Syntax Help From: Brian Modra To: "Plugge, Joe R." Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=000e0cd598309c5ba704772c0b43 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=-0.001, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/146 X-Sequence-Number: 33345 --000e0cd598309c5ba704772c0b43 Content-Type: text/plain; charset=ISO-8859-1 2009/10/30 Plugge, Joe R. > I am trying to create a function that will grind through a cdr table and > populate another table. I am trying to load the function and am getting the > following error: > > > > ERROR: function result type must be specified > > > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > AS $$ DECLARE > > you need to tell it the return type. If there is none, "returns void" e.g. CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) returns void AS $$ > mycount integer; > > BEGIN > > WHILE mystart < mystop + INTERVAL '1 day' LOOP > > SELECT INTO mycount count(*) FROM log_calls WHERE startdate < mystart > and enddate > mystop; > > INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES > (mystart,mycount); > > mystart := mystart + INTERVAL '1 minute'; > > mystop := mystop + INTERVAL '1 minute'; > > END LOOP; > > END; > > $$ LANGUAGE 'plpgsql' STABLE; > > > > > > > > > > > > [image: image001]*Joe R. Plugge* > > *Database Administrator, West Interactive Corporation* > > *11650 Miracle Hills Drive, Omaha NE 68154* > > *402-716-0349 | Cell 402-517-2710 | jrplugge@west.com** * > > > > *This electronic message transmission, including any attachments, contains > information from West Corporation which may be confidential or privileged. > The information is intended to be for the use of the individual or entity > named above. If you are not the intended recipient, be aware that any > disclosure, copying, distribution or use of the contents of this information > is prohibited. * > > * * > > *If you have received this electronic transmission in error, please notify > the sender immediately by a "reply to sender only" message and destroy all > electronic and hard copies of the communication, including attachments.* > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ --000e0cd598309c5ba704772c0b43 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
2009/10/30 Plugge, Joe R. <= JRPlugge@west.com>

I am trying to create a function that will grind through a cdr table and popu= late another table.=A0 I am trying to load the function and am getting the follo= wing error:

=A0

ERROR:=A0 function result type must be specified

=A0

=A0

CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) AS $$ DECLARE


you need to tell it the return typ= e. If there is none, "returns void"

e.g.
=A0CREATE FUN= CTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) returns void AS $$

=A0=A0=A0 mycount integer;

BEGIN

=A0=A0=A0 WHILE mystart < mystop + INTERVAL '1 day' LOOP

=A0=A0=A0 SELECT INTO mycount=A0 count(*) FROM log_calls WHERE startdate < mystart and enddate > mystop;

=A0=A0=A0 INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES (mystart,mycount);

=A0=A0=A0 mystart :=3D mystart + INTERVAL '1 minute';

=A0=A0=A0 mystop=A0 :=3D mystop + INTERVAL '1 minute';

=A0=A0=A0 END LOOP;

END;

=A0=A0=A0 $$ LANGUAGE 'plpgsql' STABLE;

=A0

=A0

=A0

=A0

=A0

3D"image001"Joe R. Plugge

Database Administrator, West Interactive Corporation

11650 Miracle Hills Drive, Omaha NE 68154

402-716-0349 | Cell 402-517-2710 | jrplugge@west.com =

=A0

This electronic me= ssage transmission, including any attachments, contains information from West Corporation which may be confidential or privileged. The information is intended to be for the use of the individual= or entity named above. If you are not the intended recipient, be aware that an= y disclosure, copying, distribution or use of the contents of this informatio= n is prohibited.

=A0

If you have receiv= ed this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message= and destroy all electronic and hard copies of the communication, including attachments.




--
Brian Modra =A0 Land li= ne: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince A= lbert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/
--000e0cd598309c5ba704772c0b43--