normalizing & join to table function - Mailing list pgsql-general

From Little, Douglas
Subject normalizing & join to table function
Date
Msg-id 8585BA53443004458E0BAA6134C5A7FB9CE8E81A@EGEXCMB01.oww.root.lcl
Whole thread Raw
List 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.Little@orbitz.com

 Description: cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Attachment

pgsql-general by date:

Previous
From: Edwin Quijada
Date:
Subject: HELP - compiling C functions for wiondws
Next
From: Adrian Klaver
Date:
Subject: Re: EXT :Re: Intermittent occurrence of ERROR: could not open relation