Thread: spliting a row to make several rows

Hi all: What a want to do is something like this:
suppose i have this record

aa--bb--cc

I guess if im able to do some sql/plsql procedure to get something like it
aa
bb
cc
(3 records, rigth?)

Thanks a lot
Gerardo

Gerardo Herzig wrote:
> Hi all: What a want to do is something like this:
> suppose i have this record
> 
> aa--bb--cc
> 
> I guess if im able to do some sql/plsql procedure to get something like it
> aa
> bb
> cc
> (3 records, rigth?)
> 
> Thanks a lot
> Gerardo
> 

dev=#select split_to_rows('aa--bb--cc','--');

split_to_rows
--------------- aa bb cc
(3 rows)


This function was written by David Fetter,
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php

CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
AS $$    SELECT (string_to_array($1, $2))[s.i]    FROM generate_series(        1,
array_upper(string_to_array($1,$2), 1)    ) AS s(i);
 
$$ language sql strict;

Cool!! Thanks a lot! I will try it as soon as possible

Gerardo

> Gerardo Herzig wrote:
>
>> Hi all: What a want to do is something like this:
>> suppose i have this record
>>
>> aa--bb--cc
>>
>> I guess if im able to do some sql/plsql procedure to get something 
>> like it
>> aa
>> bb
>> cc
>> (3 records, rigth?)
>>
>> Thanks a lot
>> Gerardo
>>
>
> dev=#select split_to_rows('aa--bb--cc','--');
>
> split_to_rows
> ---------------
>  aa
>  bb
>  cc
> (3 rows)
>
>
> This function was written by David Fetter,
> http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
>
> CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
> AS $$
>     SELECT (string_to_array($1, $2))[s.i]
>     FROM generate_series(
>         1,
>         array_upper(string_to_array($1, $2), 1)
>     ) AS s(i);
> $$ language sql strict;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>