date:20091030

Re: [SQL] slow count(CASE) query

2009-10-30 Thread Richard Huxton

Grant Masan wrote:
> 
> select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM
> (select
> count(CASE WHEN (type between 40 and 49)  THEN 1 ELSE NULL END) as ship1,
> count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2,
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3,
> count(CASE WHEN (type between 80 and 89)  THEN 1 ELSE NULL END) as ship4,
> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5
> FROM school_proj_boat where length <100
> GROUP BY type
> ORDER BY type) as koo
> 
> UNION ALL
> 
> select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as
...
> FROM school_proj_boat where length between 100 and 200
> GROUP BY type
> ORDER BY type) as koo
> 
> UNION ALL
...

First thing is to ditch the UNION ALLs. You're basically repeating the
same query.

Create a lookup table: length_codes (code, min_length, max_length)
Data: ('100100', 0, 99), ('100200', 100, 199), ...

SELECT length_code AS length, sum...
FROM (
  SELECT
lc.code AS length_code,
count(case)...
  FROM
school_proj_boat spb, length_codes lc
  WHERE
spb.length BETWEEN lc.min_length AND lc.max_length
  ) AS koo
;

It's easy to forget that you can join against a table using any
condition, it doesn't have to be equality. Here we use BETWEEN to
replace our UNIONs.

You'll want a unique constraint on length_codes.code and you should
really write a custom trigger to make sure none of the
min_length..max_length ranges overlap. In practice, you're probably only
setting this table up once so might not bother.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Function Syntax Help

2009-10-30 Thread Plugge, Joe R.

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;








[cid:[email protected]]Joe R. Plugge
Database Administrator, West Interactive Corporation
11650 Miracle Hills Drive, Omaha NE 68154
402-716-0349 | Cell 402-517-2710 | [email protected]

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

Re: [SQL] Function Syntax Help

2009-10-30 Thread Brian Modra

2009/10/30 Plugge, Joe R. 

>  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 | [email protected]** *
>
>
>
> *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: [SQL] Function Syntax Help

2009-10-30 Thread Plugge, Joe R.

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



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

2009/10/30 Plugge, Joe R. mailto:[email protected]>>
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 | [email protected]

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: [SQL] Function Syntax Help

2009-10-30 Thread Brian Modra

2009/10/30 Plugge, Joe R. 

>  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:* [email protected] [mailto:[email protected]] *On Behalf
> Of *Brian Modra
> *Sent:* Friday, October 30, 2009 2:29 PM
> *To:* Plugge, Joe R.
> *Cc:* [email protected]
> *Subject:* Re: [SQL] Function Syntax Help
>
>
>
> 2009/10/30 Plugge, Joe R. 
>
> 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 | [email protected]** *
>
>
>
> *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: [SQL] Function Syntax Help

2009-10-30 Thread Plugge, Joe R.

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: [email protected] [mailto:[email protected]] On Behalf Of Brian 
Modra
Sent: Friday, October 30, 2009 2:46 PM
To: Plugge, Joe R.
Cc: [email protected]
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. mailto:[email protected]>>
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: [email protected] 
[mailto:[email protected]] On Behalf Of 
Brian Modra
Sent: Friday, October 30, 2009 2:29 PM
To: Plugge, Joe R.
Cc: [email protected]
Subject: Re: [SQL] Function Syntax Help

2009/10/30 Plugge, Joe R. mailto:[email protected]>>
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 | [email protected]

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: [SQL] Function Syntax Help

2009-10-30 Thread Tom Lane

"Plugge, Joe R."  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

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] pg_get_functiondef and overloaded functions

2009-10-30 Thread Thomas Kellerer


Tom Lane wrote on 30.10.2009 05:44:

select pg_get_functiondef('foo(int)'::regproc)
select pg_get_functiondef('foo(int4)'::regproc)
select pg_get_functiondef('foo(integer)'::regproc)
but each time I get the error: function "foo(integer)" does not exist  
What am I missing?


You need to use regprocedure.  regproc is mainly for bootstrap purposes
--- it accepts a function name only.



Thanks, works fine. 

Where would I find a documentation of all those types? 
The section about pg_get_functiondef (and others) doesn't mention this.


Regards
Thomas


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] slow count(CASE) query

[SQL] Function Syntax Help

Re: [SQL] Function Syntax Help

Re: [SQL] Function Syntax Help

Re: [SQL] Function Syntax Help

Re: [SQL] Function Syntax Help

Re: [SQL] Function Syntax Help

Re: [SQL] pg_get_functiondef and overloaded functions

8 matches

Site Navigation

Mail list logo

Footer information