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