2009/10/30 Plugge, Joe R. <jrplu...@west.com> > 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: > > > > [postg...@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:* epai...@googlemail.com [mailto:epai...@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. <jrplu...@west.com> > > 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 | jrplu...@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/
