LIKE a set of pattern generated from a table

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: postgresql Forums <pgsql-general(at)postgresql(dot)org>
Subject: LIKE a set of pattern generated from a table
Date: 2010-02-09 17:01:25
Message-ID: 20100209180125.3ef2047a@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've a column that contain something formed by concatenating fields
from another table

create table stuff(
glued varchar(30),
...
);

insert into stuff select 'constA,' || field1 || ',' || field2 from
origin where ...;

insert into stuff select 'constB,' || field1 || ',' || field2 from
origin where ...;

I know this is terrible... but this is what I have, and I can't
change it.

Now what I'd like to do is selecting in stuff using a pattern as:

select * from stuff where glue like (
select '%,' || field1 || ',' || field2 || '%'
from origin
where ...
);

But this isn't going to work.
Any other way other than specifying all the const one by one in a
union and then look for equality?

select * from stuff where glue in (
select 'constA,' || field1 || ',' || field2 from
origin where ...
union
select 'constB,' || field1 || ',' || field2 from
origin where ...
);

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Browse pgsql-general by date

  From Date Subject
Next Message Seb 2010-02-09 17:03:06 SSL connection lost after long-lasting copy command
Previous Message Boszormenyi Zoltan 2010-02-09 16:25:02 Re: ERROR: could not load library "...": Exec format error