[SQL] Moving from Transact SQL to PL/pgSQL
Hello: I am starting a project using Postgres. The requirements are very similar to work I have done in the past using M$-SQL. Therefore, there are many Transact SQL stored procedures I need to port over to PL/pgSQL. Where would I find documentation on PL/pgSQL, with examples? How close to Oracle PL-SQL is Postgres? Would a Oracle PL-SQL book cover the basics? Remember be kind to the newbee. Kevin Duffy
[SQL] Trapping statement timeout
Hello All: I certain that for you guys that this will be trivial. What I need to do is to trap, go into an exception block, when a statement times out. Let me explain. I have a processing that creates summary records based upon records in another table. Think in terms of summary sales records for sales persons at multiple stores in multiple districts. This process can run for a while and moves lots of data around. I foresee the situation where multiple users might kick off a run for the same data at the same time. So I would like to implement a locking mechanism where by if a district is being run, another user can not kick it off. Please consider the following: create or replace FUNCTION fof_run( fundkey_in integer date_in date) returns void as $body$ DECLARE rowcnt integer; BEGIN BEGIN TRANSACTION; SET LOCAL STATEMENT_TIMEOUT = 5000 select count(*)into rowcnt from fofrun where fi_parentkey = fundkey_in and date_ = date_in; if rowcnt = 0 then insert into fofrum (fi_parentkey , date_, start_) values( fund_key_in, date_in, now ); end if; select * from fofrun where fi_parentkey = fundkey_in and date_ = date_in FOR UPDATE; SET LOCAL STATEMENT_TIMEOUT = 0 COMMIT; EXCEPTION WHEN END; The table FOFRUN will be the keymaster. So for a fund and a date if a records exists, lock it for update. But only wait five seconds for the lock. If you get the lock, all is good. If not I need to return a nice error message Question: When the SELECT FOR UPDATE fails/timeout what Error does it toss? I looked in the PostgreSQL Error Codes and did not see one that matched. When I am in the EXCEPTION block can I execute a normal SELECT against FOFRUN to determine when the prior lock was obtained, based upon the value in start_ Thank you for your attention to this matter. Kevin Duffy
[SQL] variables with SELECT statement
Hello All: I have a simple issue. Within my table there is a field DESCRIPTION that I would like to parse and split out into other fields. Within DESCRIPTION there are spaces that separate the data items. String_to_array(description, ' ') does the job very well. I need something like this to work. select string_to_array(description, ' ') as a_desc, a_desc[0] as name , a_desc[1] as type, a_desc[2] as size, from prodlist where type = 'B' Need to parse the DESCRIPTION and then reference the pieces. Your kind assistance is requested. Thanks Kevin Duffy WR Capital Management 40 Signal Rd Stamford, CT 203-504-6221
Re: [SQL] variables with SELECT statement
OK that is a syntax I have never seen. But correct we are getting close. Noticed that string_to_array does not handle double spaces very well. If there are double space between the tokens, there is "" (empty string) in the array returned. Not exactly what I expected. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 4:07 PM Cc: [email protected] Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Within my table there is a field DESCRIPTION that I would like to parse > and split out into other fields. > > Within DESCRIPTION there are spaces that separate the data items. > String_to_array(description, ' ') does the job very well. > > I need something like this to work. > > select string_to_array(description, ' ') as a_desc, > a_desc[0] as name , a_desc[1] as type, a_desc[2] as > size, from prodlist where type = 'B' You almost had it ... select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size from (select string_to_array(description, ' ') as a_desc from prodlist) as foo where a_desc[2] = 'B' -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Just testing the regexp_string_to_array
This SQL
select description, regexp_string_to_array(description::text , E'\\s+' )
as optdesc, securitytype
from xx where type = 'B' order by 1
produced this error:
ERROR: function regexp_string_to_array(text, text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may
need to add explicit type casts.
Character: 21
Don't see the difference between the above and the example in the doc's.
kd
-Original Message-
From: Osvaldo Kussama [mailto:[EMAIL PROTECTED]
Sent: Friday, September 05, 2008 4:47 PM
To: Kevin Duffy
Subject: Re: [SQL] variables with SELECT statement
2008/9/5, Kevin Duffy <[EMAIL PROTECTED]>:
> OK that is a syntax I have never seen. But correct we are getting
> close.
>
> Noticed that string_to_array does not handle double spaces very well.
> If there are double space between the tokens, there is "" (empty
string)
>
> in the array returned. Not exactly what I expected.
>
Try regexp_split_to_array().
http://www.postgresql.org/docs/current/interactive/functions-matching.ht
ml#FUNCTIONS-POSIX-REGEXP
SELECT regexp_split_to_array('the quick brownfox jumped over
the lazy dog', E'\\s+');
regexp_split_to_array
{the,quick,brown,fox,jumped,over,the,lazy,dog}
Osvaldo
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
No looks like I have 8.2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 5:13 PM Cc: [email protected] Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Just testing the regexp_string_to_array > > This SQL > select description, regexp_string_to_array(description::text , E'\\s+' ) > as optdesc, securitytype > from xx where type = 'B' order by 1 > > produced this error: > > ERROR: function regexp_string_to_array(text, text) does not exist > SQL state: 42883 > Hint: No function matches the given name and argument types. You may > need to add explicit type casts. > Character: 21 Are you running 8.3? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Thanks Mr. Lane for catching that.
If I run
SELECT regexp_split_to_array('the quick brown fox jumped over the lazy
dog', \\s+');
Straight out of the documentation I get
ERROR: function regexp_split_to_array("unknown", "unknown") does not
exist
Let me guess I have to upgrade.
kd
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Friday, September 05, 2008 5:27 PM
To: Frank Bax
Cc: [email protected]
Subject: Re: [SQL] variables with SELECT statement
Frank Bax <[EMAIL PROTECTED]> writes:
> Kevin Duffy wrote:
>> ERROR: function regexp_string_to_array(text, text) does not exist
> Are you running 8.3?
Also, it's regexp_split_to_array ...
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
When was 8.3 released? But for today I could do string_to_array(regexp_replace(description, E'\\s+', ' '), ' ') as desc and get what I need to survive. Many thanks for all the replys. Would not have made progress on this by myself. kd -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2008 5:35 PM To: Kevin Duffy Cc: [email protected]; Frank Bax Subject: Re: [SQL] variables with SELECT statement On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <[EMAIL PROTECTED]> wrote: > No looks like I have 8.2 I can attest that all of 8.3's performance improvements as well all of the really useful new functions like the one mentioned here make it well worth the effort to upgrade. I haven't been as excited about a pgsql version since vacuum (regular) was invented. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] simple SQL query
Hello: I have a couple of queries that are giving me headaches. They are actually very simple, but I do not understand why I am not getting the expected results. Maybe I need new glasses. Please be kind. The table definitions are below. The table TMP_INDEX_MEMBER contains 21057 rows. These rows contain 3167 distinct ISINs. ISIN is a type of unique security identifier. This query select * from security where securitytypekey NOT IN ( 5,27) and ISIN IN (select ISIN from tmp_index_member ) returns 3069 rows. This tells me that there are 3069 ISINs in the SECURITY table. ISINs that I already know about. update tmp_index_member set securitykey = security.securitykey from security where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = security.ISIN results in Query returned successfully: 20545 rows affected, 2169 ms execution time. There are now 512 row in TMP_INDEX_MEMBER that have not been updated. OK now the confusion begins. I would expect the following query to return 512 rows. It returns zero. select * from tmp_index_member tim where tim.ISIN NOT IN (select distinct sec.ISIN from security sec where securitytypekey NOT IN ( 5,27) ) I want to add to SECURITY the securities that are new to me. To do this I need the above query to work. Question: does a UNIQUE constraint create an index? Maybe your fresh eyes will see something obvious. Many thanks to taking a look at this issue. KD SECURITY - contains the list my in-house security list CREATE TABLE "security" ( securitykey serial NOT NULL, securitytypekey integer, securitydesc character varying(125), bbcode character(25), ric character(15), sedol character(15), cusip character(12), isin character(15), securityissuecurriso character varying(3), underlyingcusip character varying(15), ticker character(30), underlyingisin character varying(15), expirationdate date, strikeprice numeric(19,6), put_call character(1), multiplier integer, createdate timestamp without time zone DEFAULT now(), ccy1isocode character(3), ccy2isocode character(3), contractdate date, fwdrate numeric(15,8), contract character(25), contractsize integer, unitprice numeric(10,6), underlyingticker character(20), underlyingbloomberg character(20), couponrate numeric(15,8), maturitydate date, exchangekey integer, CONSTRAINT pk_security PRIMARY KEY (securitykey), CONSTRAINT fk_security_securitytype FOREIGN KEY (securitytypekey) REFERENCES securitytype (securitytypekey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT unq_security_cusip UNIQUE (cusip, securitytypekey, securityissuecurriso), CONSTRAINT unq_security_isin UNIQUE (isin, securitytypekey, securityissuecurriso) ) WITHOUT OIDS; ALTER TABLE "security" OWNER TO postgres; -- Index: security_bbcode -- DROP INDEX security_bbcode; CREATE INDEX security_bbcode ON "security" USING btree (bbcode, securitytypekey); -- Index: "security_sectype_isoCurr" -- DROP INDEX "security_sectype_isoCurr"; CREATE INDEX "security_sectype_isoCurr" ON "security" USING btree (securitytypekey, securityissuecurriso); TMP_INDEX_MEMBER - contains the members of indexes such as S&P 500 and Russell 1000 CREATE TABLE tmp_index_member ( tmp_index_member_key serial NOT NULL, index_key integer, taskrunkey integer, isin character(15), cusip character(12), sedol character(12), bbcode character(15), curr character(5), bbtype character(20), secweight numeric(19,6), securitykey integer, gics_sector integer, gics_sector_name character(75), gics_industry_group integer, gics_industry_group_name character(75), gics_industry integer, gics_industry_name character(75), bbdesc character(50), CONSTRAINT pk_tmp_index_member PRIMARY KEY (tmp_index_member_key), CONSTRAINT fk_tmpindexmember_index_ FOREIGN KEY (index_key) REFERENCES index_ (index_key) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey) REFERENCES taskrun (taskrunkey) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITHOUT OIDS; ALTER TABLE tmp_index_member OWNER TO postgres; Kevin Duffy WR Capital Management
Re: [SQL] simple SQL query
Gentlemen: Thanks so much for your assistance. This returns 512 rows. select * from tmp_index_member tim where tim.ISIN NOT IN (select ISIN from security sec where ISIN is NOT NULL and securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) ) Can someone explain why the NULL ISINs in Security is causing so much grief? I do not get it. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Joseph Krogh Sent: Wednesday, October 29, 2008 3:58 PM To: [email protected] Subject: Re: [SQL] simple SQL query On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote: > Hello: > > > > I have a couple of queries that are giving me headaches. > > They are actually very simple, but I do not understand why > > I am not getting the expected results. Maybe I need new glasses. > > Please be kind. > > > > The table definitions are below. > > > > The table TMP_INDEX_MEMBER contains 21057 rows. > > These rows contain 3167 distinct ISINs. ISIN is a type of unique > security identifier. > > > > This query > > select * from security > >where securitytypekey NOT IN ( 5,27) and ISIN IN > > (select ISIN from tmp_index_member ) > > returns 3069 rows. This tells me that there are 3069 ISINs > > in the SECURITY table. ISINs that I already know about. > > > > > > update tmp_index_member set securitykey = security.securitykey > >from security > >where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN = > security.ISIN > > results in Query returned successfully: 20545 rows affected, 2169 ms > execution time. > > > > There are now 512 row in TMP_INDEX_MEMBER that have not been updated. > > OK now the confusion begins. > > > > I would expect the following query to return 512 rows. It returns zero. > > select * from tmp_index_member tim > > where tim.ISIN NOT IN > > (select distinct sec.ISIN from security sec where securitytypekey NOT > IN ( 5,27) ) > > > > > > I want to add to SECURITY the securities that are new to me. To do this > I need the above > > query to work. I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like: ... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))... > Question: does a UNIQUE constraint create an index? Yes. > Maybe your fresh eyes will see something obvious. > > Many thanks to taking a look at this issue. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] inserts within function, within insert
Hello All: I am hoping to get some guidance from the local gurus, before I go off and write this code. Consider this scenario: I receive a file containing inventory info, make it simple, clothes items. Within the file there is info for different locations, so an inventory item can occur more than once. I.e. a certain jacket could be in the inventory at more than one location. It is also possible that an inventory item in the file could be a new item, an item that is not in my item table. When I parse and import the table there will be enough info provided, so that I can create the new inventory items. I would like to write a function that based upon the info provided for an inventory item will either return the item's key or if the item is new, create the item and then return the item's key. Say I am doing a simple insert to inventory like the following: INSERT INTO INVENTORY ( itemkey, locationkey, qty) SELECT getitemkey(itemtype, style, color, size), lockey, qty from IMPORT_INV Question: New records added to the ITEM table within getitemkey(), will they be available to inserts that follow? I.e. if record 2 in IMPORT_INV is for the same clothing item as record 27, and this a new inventory item, but for different locations. Will this new inventory item get added twice to my ITEM table. Many thanks for considering this issue. Kevin Duffy
[SQL] function - string ends with
Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy
Re: [SQL] function - string ends with
Take a look at LIKE or ILIKE kd From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Duffy Sent: Friday, November 21, 2008 11:31 AM To: [email protected] Subject: [SQL] function - string ends with Hello All: Is anyone aware of a function in PgSQL that will check if a string ends with a specific string? I.e. rposition(substring in string ) returns int Starts searching right to left within string Thanks for your attention to this matter. Kevin Duffy
[SQL] simple text parsing function
Hello All:
I am requesting your kind assistance with a simple text parsing
function.
The source code for the function is below. What is interesting is that
the
path through the code that actually does something works. The simple
path through the code fails.
For example
select getrfs_bbcode('CHF/USD 12/17/2008 CURNCY ', null, null);
returns "CHFUSD CURNCY " this is correct.
select getrfs_bbcode('BPZ8 CURNCY ', NULL, NULL);
returns nothing. I need to receive 'BPZ8 CURNCY ' in this
case.
What am I missing?
Many thanks for your assistance.
KD
CREATE OR REPLACE FUNCTION getrfs_bbcode( bbcode_in bpchar,
sectype_bbsuffix_in bpchar, sectypekey_in integer )
RETURNS character AS
$BODY$
DECLARE
rtn_bbcode char(25);
tmp_bbcode char(25);
abbcode char(10)[3];
BEGIN
tmp_bbcode := upper( rtrim(bbcode_in ) );
--
if tmp_bbcode like '%CURNCY' then
if strpos( tmp_bbcode, '/' ) > 0 then
-- Is there / in the BB Code
abbcode := string_to_array(tmp_bbcode, ' ');
rtn_bbcode := replace(abbcode[1], '/', '') ||' '|| abbcode[3];
else
rtn_bbcode := tmp_bbcode; -- simple pass though case does
not work?!?!
end if;
end if;
return rtn_bbcode ;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Kevin Duffy
[SQL] where-used function
Hello All: Hope everyone is enjoying a peaceful holiday season. I am using this quite time between the holidays to get my dev and production environment back into sync. It would be very helpful, if I had a where-used function. This function, given an object name, would determine if the object exists in the database ( i.e. is it a table or a view) and then, most important, what other objects are dependant on the object. A database I worked with years ago, distributed by a marketing company in the NorthWest USA, had such a function. As I remember that function was buggy. Does anybody know if a where-used function exists for the wonderful database known as Postgres? Many Thanks Kevin Duffy
[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;
Re: [SQL] simple SQL question
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:[email protected]] 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: [email protected] [mailto:[email protected]] On Behalf Of Kevin Duffy Sent: March 20, 2009 3:06 PM To: [email protected] 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 <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
