Function Syntax Help

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

    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 | jrplugge@...

 

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.

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

 

 

 

From: epailty@... [mailto:epailty@...] On Behalf Of Brian Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: pgsql-sql@...
Subject: Re: [SQL] Function Syntax Help

 

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

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 | jrplugge@...

 

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/