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/