> Hello, > Need some help. Hoping some of the smart people might know how to solve this. > > I'd like to replace all name/value pairs in a delimited string with the id > of the name/value pair in my reference table. > Eg > St=IL&city=Chicago&street=Madison > To > 13&50&247 > Assuming St=IL is id 13, city=Chicago=50, street=Madison=247 > > My process is working but it's taking too long to convert the string to rows. > Any ideas for swaping out the cursor for a sql trick?
Hello, I would try to first explode all at once to a temp table using regexp_split_to_table, then update with your IDs, and aggregate back to the desired form. regards, Marc Mamin > Thanks in advance > Doug > > I'm currently cursoring thru the input rows, and then converting the string > to rows using 1st array_to_string, now explode_array. > -- Current code > sql_cmd := ' SELECT hash_page , log_cs_uri_query FROM > dim_wtlog_page_temp '; > FOR recset IN EXECUTE sql_cmd LOOP > insert into pagetag_temp (hash_page, wtlog_tagvalue_text) > select recset.hash_page ,qry.* as wtlog_tagvalue_text > from explode_array (string_to_array(recset.log_cs_uri_query,'&') ) > qry ; > END LOOP; > > create or replace function explode_array( in_array anyarray) > returns setof anyelement as > $$ > select ($1)[s] from generate_series(1,array_upper($1, 1)) as s; > $$ > language sql immutable; > > Doug Little > > Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz > Worldwide > 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax > 312.894.5164 | Cell 847-997-5741 > douglas.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com> > [cid:image001.jpg@01CCE011.A46685F0] orbitz.com<http://www.orbitz.com/> | > ebookers.com<http://www.ebookers.com/> | > hotelclub.com<http://www.hotelclub.com/> | > cheaptickets.com<http://www.cheaptickets.com/> | > ratestogo.com<http://www.ratestogo.com/> | > asiahotels.com<http://www.asiahotels.com/> > > >
<<attachment: image001.jpg>>
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
