From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: normalizing & join to table function |
Date: | 2012-01-31 21:49:34 |
Message-ID: | C4DAC901169B624F933534A26ED7DF3103E91834@JENMAIL01.ad.intershop.net |
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?
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(dot)Little(at)orbitz(dot)com<mailto:Douglas(dot)Little(at)orbitz(dot)com>
> [cid:image001(dot)jpg(at)01CCE011(dot)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 | Content-Type | Size |
---|---|---|
image/jpeg | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2012-01-31 22:37:45 | Re: [HACKERS] pg_dump -s dumps data?! |
Previous Message | Tom Lane | 2012-01-31 21:43:03 | Re: list blocking queries |