pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Plpgsql: Iterating through a string of parameters
3+ messages / 2 participants
[nested] [flat]

* Plpgsql: Iterating through a string of parameters
@ 2010-03-25 15:16  Leif Biberg Kristensen <leif@solumslekt.org>
  0 siblings, 2 replies; 3+ messages in thread

From: Leif Biberg Kristensen @ 2010-03-25 15:16 UTC (permalink / raw)
  To: pgsql-sql

I'm struggling with how to make plpgsql iterate through a list of numbers 
input as a text string, eg. "1438 2656 973 4208". I figure that I can use the 
regexp_split_to_array() function to make an array of the string, but can I 
iterate through an array with eg. a FOR loop?

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Plpgsql: Iterating through a string of parameters
@ 2010-03-25 16:00  Leif Biberg Kristensen <leif@solumslekt.org>
  parent: Leif Biberg Kristensen <leif@solumslekt.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Leif Biberg Kristensen @ 2010-03-25 16:00 UTC (permalink / raw)
  To: pgsql-sql

On Thursday 25. March 2010 16.16.53 Leif Biberg Kristensen wrote:
> I'm struggling with how to make plpgsql iterate through a list of numbers 
> input as a text string, eg. "1438 2656 973 4208". I figure that I can use the 
> regexp_split_to_array() function to make an array of the string, but can I 
> iterate through an array with eg. a FOR loop?

I found a solution myself through trial-and-error:

CREATE OR REPLACE FUNCTION text_to_arr(TEXT) RETURNS VOID AS $$
DECLARE
    arr TEXT ARRAY;

BEGIN
    arr := regexp_split_to_array($1, E'\\s+');
    FOR i IN 1..array_length(arr, 1) LOOP
        RAISE NOTICE '%', arr[i]::INTEGER;
    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql IMMUTABLE;

pgslekt=> select * from text_to_arr('1438 2607 1504');
NOTICE:  1438
NOTICE:  2607
NOTICE:  1504
 text_to_arr
-------------

(1 row)

pgslekt=>

regards,
-- 
Leif Biberg Kristensen
http://solumslekt.org/



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Plpgsql: Iterating through a string of parameters
@ 2010-03-26 08:49  Jasen Betts <jasen@xnet.co.nz>
  parent: Leif Biberg Kristensen <leif@solumslekt.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Jasen Betts @ 2010-03-26 08:49 UTC (permalink / raw)
  To: pgsql-sql

On 2010-03-25, Leif Biberg Kristensen <leif@solumslekt.org> wrote:
> I'm struggling with how to make plpgsql iterate through a list of numbers 
> input as a text string, eg. "1438 2656 973 4208". I figure that I can use the 
> regexp_split_to_array() function to make an array of the string, but can I 
> iterate through an array with eg. a FOR loop?

using regex_split_to_table with for is easier

 for x in regex_split_to_table(....) do
 
But if you must you can use split to array and then use the array measuring
functions to determine the limit for a counted for loop.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2010-03-26 08:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-25 15:16 Plpgsql: Iterating through a string of parameters Leif Biberg Kristensen <leif@solumslekt.org>
2010-03-25 16:00 ` Leif Biberg Kristensen <leif@solumslekt.org>
2010-03-26 08:49 ` Jasen Betts <jasen@xnet.co.nz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox