Search Postgresql Archives

Re: normalizing & join to table function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Title: AW: normalizing & join to table function

> 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.Little@xxxxxxxxxx<mailto:Douglas.Little@xxxxxxxxxx>
>  [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
Description: image001.jpg

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux