From: | "Kevin Duffy" <KD(at)wrinvestments(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | simple SQL question |
Date: | 2009-03-20 19:06:01 |
Message-ID: | DFC309C8A42633419600522FA8C4AE1AB6C154@mail-01.wrcapital.corp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Staten Oliver | 2009-03-20 19:54:18 | Postgresql Rules |
Previous Message | Alvaro Herrera | 2009-03-19 18:11:54 | Re: Re: [SQL] Relatorio da composiçao de FKs e PKs |