pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Splitting text into rows with SQL
2+ messages / 2 participants
[nested] [flat]
* Re: Splitting text into rows with SQL
@ 2003-03-07 22:08 Ryan <pgsql-sql@seahat.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ryan @ 2003-03-07 22:08 UTC (permalink / raw)
To: pgsql-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...
<-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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Splitting text into rows with SQL
@ 2003-03-07 22:14 Chris Gamache <cgg007@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Chris Gamache @ 2003-03-07 22:14 UTC (permalink / raw)
To: pgsql-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-0000000000000000
2 | something3 | 11111111-1111-1111-1111111111111111
3 | 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/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-03-07 22:14 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 22:08 Re: Splitting text into rows with SQL Ryan <pgsql-sql@seahat.com>
2003-03-07 22:14 Splitting text into rows with SQL Chris Gamache <cgg007@yahoo.com>
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