Обсуждение: Splitting text into rows with SQL

Using Postgresql 7.2.3 ...

In order to search using indexes I need to split a composite field into its
components and return it as rows... If this would only work:

<theoretical code>

create table table_with_composite_fields (
data1 serial,
data2 varchar(100),
composite_field text
);

insert into table_with_composite_fields (data2, composite_field) values

('something1','00000000-0000-0000-0000000000000000,11111111-1111-1111-1111111111111111,22222222-2222-2222-2222222222222222');


create table other_table (
data3 serial,
data4 varchar(100),
uuid uniqueidentifier
);

create index 'other_table_uuid_idx' on other_table(uuid);

insert into other_table (data4, uuid) values
('something2','00000000-0000-0000-0000000000000000');

insert into other_table (data4, uuid) values
('something3','11111111-1111-1111-1111111111111111');

insert into other_table (data4, uuid) values
('something4','22222222-2222-2222-2222222222222222');

select * from other_table ot where ot.uuid in (select split(composite_field)
from table_with_composite_field where data1=1) order by data3;
data3 |   data4    |               uuid
-------+------------+-------------------------------------1     | something2 | 00000000-0000-0000-00000000000000002
|something3 | 11111111-1111-1111-11111111111111113     | something4 | 22222222-2222-2222-2222222222222222
 

</theoretical code>

any ideas for creating my fictional "split" function? I don't mind if the
solution is head-slapping-ly simple. I'm too close to the problem and can't
seem to figure it out!

CG

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

> Using Postgresql 7.2.3 ...
>
> In order to search using indexes I need to split a composite field
> into its components and return it as rows...
<-SNIP->
> any ideas for creating my fictional "split" function? I don't mind if
> the solution is head-slapping-ly simple. I'm too close to the problem
> and can't seem to figure it out!
>
> CG
Take a look at contrib/fulltextindex/README.fti folder in the source
dist.

This should do exactly what you need.

Ryan