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

On Mon, 26 Oct 2009 14:15:26 +0100
Ivan Sergio Borgonovo <mail@webthatworks.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@example1.com','m@example2.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