Re: Function Syntax Help

2009/10/30 Plugge, Joe R. <JRPlugge(at)west(dot)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:
>
>
>
> [postgres(at)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(at)googlemail(dot)com [mailto:epailty(at)googlemail(dot)com] *On Behalf
> Of *Brian Modra
> *Sent:* Friday, October 30, 2009 2:29 PM
> *To:* Plugge, Joe R.
> *Cc:* pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] Function Syntax Help
>
>
>
> 2009/10/30 Plugge, Joe R. <JRPlugge(at)west(dot)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 | jrplugge(at)west(dot)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/