SELECT coalsce((select action
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.
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] 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
>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
> IF emailAddress is present in blocked THEN
> return action associated with emailAddress -- Action is
> return 'OK'
>I've created the table
>CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
> email TEXT NOT NULL UNIQUE ,
> action TEXT NOT NULL DEFAULT 'DISCARD')
>I can insert values
>insert into blocked (email) values('rwillett.dr...@example.com')
>insert into blocked (email) values('rwillett+dr...@example.com')
>And this is the code that I have written that works but it looks poor
>my untutored eyes
> WHEN EXISTS (select 1 from blocked where email =
> THEN (select action from blocked where email =
> ELSE 'OK'
>In the Postfix query I'll replace
>email = 'rwillett.dr...@example.com'
>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
>Is there a better way under SQLite?
>Any suggestions welcomed.
>sqlite-users mailing list
sqlite-users mailing list