Thanks, I changed my code to this, it compiled, and it seems to be running now:
CREATE OR REPLACE FUNCTION gen_simultaneous_calls(mystart timestamp, mystop
timestamp) RETURNS VOID AS $$
DECLARE
mycount integer;
newstart timestamp := mystart;
newstop timestamp := mystop;
BEGIN
WHILE newstart < newstop + INTERVAL '1 day' LOOP
SELECT INTO mycount count(*) FROM log_calls WHERE startdate < newstart and
enddate > newstop;
INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES
(newstart,mycount);
newstart := newstart + INTERVAL '1 minute';
newstop := newstop + INTERVAL '1 minute';
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;
From: epai...@googlemail.com [mailto:epai...@googlemail.com] On Behalf Of Brian
Modra
Sent: Friday, October 30, 2009 2:46 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<mailto: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>
[mailto: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<mailto:pgsql-sql@postgresql.org>
Subject: Re: [SQL] Function Syntax Help
2009/10/30 Plugge, Joe R. <jrplu...@west.com<mailto: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;
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<mailto: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/
