Thread: Function Syntax Help

Function Syntax Help

From

"Plugge, Joe R."

Date:

30 October 2009, 19:21:50

<div class="Section1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">I am trying
tocreate a function that will grind through a cdr table and populate another table.  I am trying to load the function
andam getting the following error:</span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> </span><pclass="MsoPlainText">ERROR:  function result type
mustbe specified<p class="MsoPlainText"> <p class="MsoPlainText"> <p class="MsoPlainText">CREATE FUNCTION
gen_simultaneous_calls(mystarttimestamp, mystop timestamp) AS $$ DECLARE<p class="MsoPlainText">    mycount integer;<p
class="MsoPlainText">BEGIN<pclass="MsoPlainText">    WHILE mystart < mystop + INTERVAL '1 day' LOOP<p
class="MsoPlainText">   SELECT INTO mycount  count(*) FROM log_calls WHERE startdate < mystart and enddate >
mystop;<pclass="MsoPlainText">    INSERT INTO simultaneous_calls_rpt (startdate,call_count) VALUES (mystart,mycount);<p
class="MsoPlainText">   mystart := mystart + INTERVAL '1 minute';<p class="MsoPlainText">    mystop  := mystop +
INTERVAL'1 minute';<p class="MsoPlainText">    END LOOP;<p class="MsoPlainText">END;<p class="MsoPlainText">    $$
LANGUAGE'plpgsql' STABLE;<p class="MsoPlainText"> <p class="MsoPlainText"> <p class="MsoPlainText"> <p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Tahoma","sans-serif""> </span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> </span><pclass="MsoNormal"><img align="left" alt="image001"
height="66"hspace="12" src="cid:image002.jpg@01CA596B.59B9EC10" v:shapes="Picture_x0020_3" width="79" /><b><span
style="font-size:10.0pt;
font-family:"Verdana","sans-serif";color:#999999">Joe R. Plugge</span></b><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""></span><pclass="MsoNormal"><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";
color:#999999">Database Administrator, West Interactive Corporation</span></b><p class="MsoNormal"><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";
color:#999999">11650 Miracle Hills Drive, Omaha NE 68154</span></b><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""></span><pclass="MsoNormal"><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";
color:#999999">402-716-0349 | Cell 402-517-2710 | jrplugge@west.com</span></b><b><span
style="font-size:8.0pt;font-family:"Verdana","sans-serif";color:navy"></span></b><span
style="font-size:10.0pt;font-family:"Verdana","sans-serif""></span><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><i><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif"; 
color:black">This electronic message transmission, including any attachments, contains information from West
Corporationwhich may be confidential or privileged. The information is intended to be for the use of the individual or
entitynamed above. If you are not the intended recipient, be aware that any disclosure, copying, distribution or use of
thecontents of this information is prohibited. </span></i><p class="MsoNormal"><i><span
style="font-size:8.0pt;font-family:"TimesNew Roman","serif"; 
color:black"> </span></i><p class="MsoNormal"><i><span style="font-size:8.0pt;font-family:"Times New Roman","serif";
color:black">If you have received this electronic transmission in error, please notify the sender immediately by a
"replyto sender only" message and destroy all electronic and hard copies of the communication, including
attachments.</span></i></div>

Re: Function Syntax Help

From

Brian Modra

Date:

30 October 2009, 19:29:37

2009/10/30 Plugge, Joe R. <JRPlugge@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 | 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/

Re: Function Syntax Help

From

"Plugge, Joe R."

Date:

30 October 2009, 19:31:03

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@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. <JRPlugge@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 | 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/

Re: Function Syntax Help

From

Brian Modra

Date:

30 October 2009, 19:45:50

2009/10/30 Plugge, Joe R. <JRPlugge@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:

[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. <JRPlugge@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 | 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/

Re: Function Syntax Help

From

"Plugge, Joe R."

Date:

30 October 2009, 19:51:05

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: epailty@googlemail.com [mailto:epailty@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. <JRPlugge@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:

[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. <JRPlugge@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 | 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/

Re: Function Syntax Help

From

Tom Lane

Date:

30 October 2009, 21:22:29

"Plugge, Joe R." <JRPlugge@west.com> writes:
> Thanks, I changed my code to this, it compiled, and it seems to be running now:

It looks like you are expecting assignment to the input parameters to do
something useful ... it will not.  Maybe you need some output
parameters?
        regards, tom lane