[SQL] simple SQL question

[Kevin Duffy]

Hello All:

 

I would like your input on how I should approach a problem.

 

Say I have a table of companies and one attribute is the market
capitalization of these companies.

I have another table (definition below) and it contains capitalization
levels. 

For example Micro Cap, Mid Cap, and Large Cap.However, the table 

CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels.

 

The question is: What is the most efficient way to assign/join the
capitalization levels to the companies?

 

I could create a function that given a market cap in millions would
return the matching cap level,

by using a cursor to step through CAPITALIZATIONLEVEL  from lowest to
highest. 

This function would be declared STABLE.

 

-or maybe-

 

a function that RETURNS SETOF and the rows in the set returned would
contain both the lower and 

upper limits of the cap level.  The lower limit would be calc'ed  by
using a cursor to step through 

CAPITALIZATIONLEVEL  from lowest to highest.  This function would be
declared STABLE.

 

 

Which method would execute more efficiently?  

 

Thanks for considering my issue.

 

Kevin Duffy

 

 

 

CREATE TABLE capitalizationlevel

(

  capitallevelkey serial NOT NULL,

  caplevelname character(10) NOT NULL,

  caplevelmillions integer NOT NULL, <-- this is the upper limit

  CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey)

)

WITH (OIDS=FALSE);

ALTER TABLE capitalizationlevel OWNER TO postgres;

[SQL] Postgresql Rules

[Staten Oliver]

I have three tables (CUST_CNTCTS, CUST_CO_BILL_ADDR,  and
CUST_CNTCT_MAILING_LIST_ADDR) using postgresql 8.1.10

CUST_CNTCTS contains contacts for companies we do business with

CUST_CO_BILL_ADDR contains billing address for these companies

CUST_CNTCT_MAILING_LIST_ADDR associates contacts with addresses so we
know where to send marketing information for each contact 

I have a rule (that works correctly) in CUST_CNTCTS that removes a
contact from CUST_CNTCT_MAILING_LIST_ADDR when their "mailing_list" flag
is turned from true to false

CREATE OR REPLACE RULE "mailing_list_to_malng-lst" 
AS ON UPDATE TO meedb."CUST_CNTCTS"
WHERE old.mailing_list <> new.mailing_list AND new.mailing_list = false 
DO  DELETE FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
WHERE new.cust_cntct_id = "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id;
COMMENT ON RULE "mailing_list_to_malng-lst" ON meedb."CUST_CNTCTS" IS 'If 
mailing_list is changed from true to false, remove the contact from the mailing 
list';

There is also a rule in CUST_CO_BILL_ADDR that sets
CUST_CNTCTS."mailing_list" to false when the address for a contact (that
is in CUST_CNTCT_MAILING_LIST_ADDR) is deactivated

CREATE OR REPLACE RULE "status_to_malng-lst" AS
ON UPDATE TO meedb."CUST_CO_BILL_ADDR"
WHERE old.status::text <> new.status::text AND new.status::text = 'i'::text 
DO  UPDATE meedb."CUST_CNTCTS" SET mailing_list = false
WHERE ("CUST_CNTCTS".cust_cntct_id IN 
(SELECT "CUST_CNTCT_MAILING_LIST_ADDR".cust_cntct_id
 FROM meedb."CUST_CNTCT_MAILING_LIST_ADDR"
 WHERE "CUST_CNTCT_MAILING_LIST_ADDR".cust_bill_addr_id = 
new.cust_bill_addr_id));
COMMENT ON RULE "status_to_malng-lst" ON meedb."CUST_CO_BILL_ADDR" IS 'If the 
status of a company''s billing address is changed from active to inactive, 
remove the contacts associated with this address from the mailing list by 
setting the mailing_list flag in CUST_CNTCTS to FALSE';

The problem is that when I change the status of the address from a
(active) to i (inactive) the contacts associated with the address are
removed from CUST_CNTCT_MAILING_LIST_ADDR, but
CUST_CNTCTS."mailing_list" is not set to FALSE

Does anyone see any problems with what I am doing, or is this something
that can't be done with postgresql's rule system?

Re: [SQL] simple SQL question

[Kevin Duffy]

Mr. McFadyen:

 

Thanks for your quick reply.

 

The coloumns in the SECURITY Table of interest would be:

  securitykey serial NOT NULL,

  securitytypekey integer,

  securitydesc character varying(125),

  marketcap_usd numeric(19,6)

 

where marketcap_usd would be used to join into capitalizationlevel.

 

 

 

I was hoping to create something, how shall we say, tidy".

 

So I could write something like 

  Select SECURITY.* ,   getcaplevelkey(  marketcap_usd ) From
security

 

 

Or 

 

Select security.*,  caplevelkey, caplevelname

  From SECURITY,  getcaplevel()  as gcl

  Where  marketcap_usd between ( gcl.caplow  and gcl.caphigh )

 

 

I would expect that in the first option the function would be called for
every security and would have to loop through a cursor for each call.

In the second option where the function is returning a SETOF,  how many
times would it be called?

If I put it in parentheses, how many times would it be called?

 

Any comments from the PostgresSQL gurus out there?

 

Happy Friday.

 

KD

 

 

 



From: Dan McFadyen [mailto:d...@cryptocard.com] 
Sent: Friday, March 20, 2009 3:51 PM
To: Kevin Duffy
Subject: RE: [SQL] simple SQL question

 

Hi again,

 

You got me curious so I went into my SQL browser and got cracking:

 

SELECT companyName, capName FROM

(

SELECT companyName, MIN(capitalizationlevel.caplevelmillions) FROM
companies, capitalizationlevel WHERE companymillions <
capitalizationlevel.caplevelmillions GROUP BY companyName

) as something JOIN capitalizationlevel ON something.min=
capitalizationlevel.caplevelname

 

 

I don't know what your company table looks like, so that's the closest I
can get.

 

Enjoy

 

From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Kevin Duffy
Sent: March 20, 2009 3:06 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] simple SQL question

 

Hello All:

 

I would like your input on how I should approach a problem.

 

Say I have a table of companies and one attribute is the market
capitalization of these companies.

I have another table (definition below) and it contains capitalization
levels. 

For example Micro Cap, Mid Cap, and Large Cap.However, the table 

CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels.

 

The question is: What is the most efficient way to assign/join the
capitalization levels to the companies?

 

I could create a function that given a market cap in millions would
return the matching cap level,

by using a cursor to step through CAPITALIZATIONLEVEL  from lowest to
highest. 

This function would be declared STABLE.

 

-or maybe-

 

a function that RETURNS SETOF and the rows in the set returned would
contain both the lower and 

upper limits of the cap level.  The lower limit would be calc'ed  by
using a cursor to step through 

CAPITALIZATIONLEVEL  from lowest to highest.  This function would be
declared STABLE.

 

 

Which method would execute more efficiently?  

 

Thanks for considering my issue.

 

Kevin Duffy

 

 

 

CREATE TABLE capitalizationlevel

(

  capitallevelkey serial NOT NULL,

  caplevelname character(10) NOT NULL,

  caplevelmillions integer NOT NULL, <-- this is the upper limit

  CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey)

)

WITH (OIDS=FALSE);

ALTER TABLE capitalizationlevel OWNER TO postgres;

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Statements and opinions expressed in this e-mail may not
represent those of the company. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended
recipient is prohibited. If you received this in error, please contact
the sender immediately and delete the material from any computer.
Please see our legal details at http://www.cryptocard.com
 
CRYPTOCard Inc. is registered in the province of Ontario, Canada with
Business number 80531 6478.  CRYPTOCard Europe is limited liability
company registered in England and Wales (with registered number 05728808
and VAT number 869 3979 41); its registered office is Eden Park, Ham
Green, Bristol, BS20 0EB