SELECT coalsce((select action 
                  from blocked 
                 where mail='...'), 'OK') as action;

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>] On Behalf Of Rob Willett
>Sent: Wednesday, 16 May, 2018 02:22
>Subject: [sqlite] Is this really the best way to do this?
>I'm experimenting with an email server, Mail In a Box. It's a free
>product and gives you a mail server in a box with SSL certificates,
>multiple domains and seems to work well.
>One thing it doesn't do is allow people to specify emails to block.
>It uses SQLite to store the underlying data necessary to power
>Dr Richard Hipp, add another new application to your list :)
>I've worked out how Postfix calls SQLite (pretty easy), worked out
>I want to do, and am trying to write a single line of SQL that does
>The need is that the SQL must generate a single string answer
>on the email address that is fed in through the query. There are no
>options here, it must return a value even if there is nothing in the
>table. It is not practical to add a table with every email address
>returns OK.
>For this example the only strings it can return are 'OK' and
>though the RFC allows other strings. If a query is done on an email
>it is blocked then it must return DISCARD (or an action in the action
>column. If no email is in the table matching then it must return
>As a piece of pseudo code this would ne
>function GetMailStatus(emailAddress)
>       IF emailAddress is present in blocked THEN
>               return action associated with emailAddress -- Action is
>  return 'OK'
>I've created the table
>                                          email TEXT NOT NULL UNIQUE ,
>                       action TEXT NOT NULL DEFAULT 'DISCARD')
>I can insert values
>insert into blocked (email) values('')
>insert into blocked (email) values('')
>And this is the code that I have written that works but it looks poor
>my untutored eyes
>select CASE
>     WHEN EXISTS (select 1 from blocked where email =
>     THEN (select action from blocked where email =
>     ELSE 'OK'
>     END
>In the Postfix query I'll replace
>email = ''
>with email = '%s'
>so that the email address is passed in.
>My worry is that the query has two selects and 'feels' bad. It works
>feels dirty...
>Is there a better way under SQLite?
>Any suggestions welcomed.
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to