Re: joining an array with a table or...?

On Mon, 26 Oct 2009 14:15:26 +0100
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

> I've a list of emails and a list of users (with emails).
>
> If the list of emails was already inside a table
>
> create table mails (
> mail varchar(64)
> );
>
> create table users (
> name varchar(127),
> mail varchar(64)
> );
>
> I'd do:
> select coalesce(u.mail, m.mail) from mails left join users on
> u.mail=m.mail;
>
> Now mails are into a php array and they may be in the range of 5000
> but generally less.
>
> The final query will be something like
> insert into mailqueue (qid, uid, mail, ...) select ...
>
> and since some fields are pseudo random sequences computed from a
> serial, it would be "clean" to do it just in one query.

> Any clean technique?

To make it more concrete I came up with:

select coalesce(u.mail,j.mail) from (
select (array['m(at)example1(dot)com','m(at)example2(dot)com'])[i] as mail
from generate_series(1,2) i) j
left join users u on upper(u.mail)=upper(j.mail);

but I sincerely dislike it.

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