help with normalizing

From: "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: help with normalizing
Date: 2012-01-28 22:31:45
Message-ID: 8585BA53443004458E0BAA6134C5A7FB9CE8DDFB@EGEXCMB01.oww.root.lcl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
Douglas(dot)Little(at)orbitz(dot)com<mailto:Douglas(dot)Little(at)orbitz(dot)com>
[cid:image001(dot)jpg(at)01CCDDD9(dot)A969D9B0] 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/>

Browse pgsql-general by date

  From Date Subject
Next Message Allan Kamau 2012-01-29 06:30:37 PG migration policy
Previous Message Oliver Kohll - Mailing Lists 2012-01-28 19:52:02 Re: Multi master use case?