Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 593E4640C55 for ; Fri, 30 Oct 2009 16:45:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68505-05 for ; Fri, 30 Oct 2009 19:45:41 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f181.google.com (mail-yx0-f181.google.com [209.85.210.181]) by mail.postgresql.org (Postfix) with ESMTP id 66C85640C4D for ; Fri, 30 Oct 2009 16:45:41 -0300 (ADT) Received: by yxe11 with SMTP id 11so2925803yxe.15 for ; Fri, 30 Oct 2009 12:45:40 -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=dqpJ3N664Zhr96FJ7e9yLlHfOPSBgTEB+xyVEcXqCZ0=; b=nonCNKmoiIv7ncmW7MLefDwg77i6j/gkvqlpuIHFfpZHHZfbROZaU6TjXpLwX+m5X4 R8Ga/ljoMI3DidmxsLK6OKAwxOUTKn0/x5fvnR0fWi0b3Smy0NnWEI/BIeAcMtg5XAbQ ktCwQXm2qXSVGAWGfkcxF/JtnzvpW53GFJ7Aw= 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=MRVnADEuiBF6uGgRNndiB5c5QUW5QnbIBpBkyT0g9OHj87aR6jEHfPhUiLvMpBelsY 1TD9+LR6a96N8KZt8+453JKwqzffhOUPtYvqwdzDIhycDjz2bjdphEgnBx5qvj7sCaCC WGuS6tDunWefpkBF967d5XNqzvYdoBVz32KcE= MIME-Version: 1.0 Received: by 10.150.46.38 with SMTP id t38mr3794709ybt.121.1256931940052; Fri, 30 Oct 2009 12:45:40 -0700 (PDT) In-Reply-To: References: <5a9699850910301229r6e3b936g7c86d2ab0cdaa6c6@mail.gmail.com> Date: Fri, 30 Oct 2009 21:45:40 +0200 X-Google-Sender-Auth: c1872071e4ece142 Message-ID: <5a9699850910301245y4cef2adcob42ea03fc2b68634@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=000e0cd5a306cb30e804772c4595 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/148 X-Sequence-Number: 33347 --000e0cd5a306cb30e804772c4595 Content-Type: text/plain; charset=ISO-8859-1 2009/10/30 Plugge, Joe R. > Thanks Brian, I changed it to this: > > > > CREATE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) > RETURNS VOID AS $$ > > DECLARE > > 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; > > > > But now am getting a different error: > > > > [postgres@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly > > ERROR: "$1" is declared CONSTANT > > CONTEXT: compilation of PL/pgSQL function "gen_simultaneous_calls" near > line 7 > > mystart and mystop are constants... you could declare variables and copy those into them, and the modify the new variables... > > > > > > > *From:* epailty@googlemail.com [mailto:epailty@googlemail.com] *On Behalf > Of *Brian Modra > *Sent:* Friday, October 30, 2009 2:29 PM > *To:* Plugge, Joe R. > *Cc:* pgsql-sql@postgresql.org > *Subject:* Re: [SQL] Function Syntax Help > > > > 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/ > -- 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/ --000e0cd5a306cb30e804772c4595 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 2009/10/30 Plugge, Joe R. <JRPlugge@west.com>

Thank= s Brian, I changed it to this:

=A0

CREAT= E FUNCTION gen_simultaneous_calls(mystart timestamp, mystop timestamp) RETURNS VOID AS $$

DECLA= RE

=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;<= /span>

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

=A0

But now am getting a different error:

=A0

[post= gres@linux1559 ~]$ cat gen_simultaneous_calls.sql | psql holly

ERROR= :=A0 "$1" is declared CONSTANT

CONTE= XT:=A0 compilation of PL/pgSQL function "gen_simultaneous_calls" near line 7


=A0mystart and mystop are constants= ...

you could declare variables and copy those into them, and the mo= dify the new variables...

=A0

=A0

=A0

From:= epailty@googlem= ail.com [mailto:epailty@googlemail.com] On Behalf Of Brian Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: pg= sql-sql@postgresql.org
Subject: Re: [SQL] Function Syntax Help

=A0

2009/10/30 Plugge, Joe R. <JRPlugge@west.com>

I am trying to crea= te a function that will grind through a cdr table and populate another table.=A0 I am trying to load the function and am getting the following error:

=A0

ERROR:=A0 function result type must be specified

=A0

=A0

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


you need to tell it the return type. If there is none, "returns void&q= uot;

e.g.
=A0CREATE FUNCTION 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

Thi= s 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 us= e 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.

=A0=

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




--
Brian Modra =A0 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.zwartber= g.com/




--
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/
--000e0cd5a306cb30e804772c4595--