Please help with syntax of sql_clause within policy_bank

[Tilman Schmidt]

On 12.05.2016 01:48, Daniel Funke wrote:
> I tried:
> 
> $policy_bank{'IN_PRE_QUEUE'} = {
>   syslog_ident              => 'amavis-prequeue',
>   sql_clause  =>  { 'sel_policy' => "SELECT * FROM amavisd_select_policy
> WHERE policy_bank='IN_PRE_QUEUE' AND users.email IN (%k)" },
> }
> 
> And here is my debug output:
> 
[...]
> 2016-05-12T01:43:05.447732+02:00 mail3 amavis-prequeue[16363]:(16363-01)
> (!)lookup_sql: sql exec: err=7, 42P01, DBD::Pg::st execute failed:
> ERROR:  missing FROM-clause entry for table "users"\nLINE 1:
> ...elect_policy WHERE policy_bank='IN_PRE_QUEUE' AND users.emai...\n ^
> at (eval 94) line 172., ,

The message should be clear enough:
Your FROM says you want to work with table "amavisd_select_policy"
but later in the second term of your WHERE clause you try to use
field "email" of table "users". That's not legal in SQL. The FROM
clause must list all tables that will be used in the query.

The correct solution depends of course entirely on the structure of
your database. My crystal ball says you'll need some kind of join.
But you know how it is with crystal balls these days. Can't get a
decent one without spending a fortune.

HTH
Tilman