Please help with syntax of sql_clause within policy_bank

[Daniel Funke]

Hi,

I want to read most settings within a policy bank from sql. I'm not sure
with the syntax, but 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.438728+02:00 mail3 amavis-prequeue[16363]:(16363-01)
lookup_sql select: SELECT * FROM amavisd_select_policy WHERE
policy_bank='IN_PRE_QUEUE' AND users.email IN (?,?,?,?,?,?)
2016-05-12T01:43:05.438880+02:00 mail3 amavis-prequeue[16363]:(16363-01)
sql begin, nontransaction
2016-05-12T01:43:05.439091+02:00 mail3 amavis-prequeue[16363]:(16363-01)
Connecting to SQL database server
2016-05-12T01:43:05.439256+02:00 mail3 amavis-prequeue[16363]:(16363-01)
connect_to_sql: trying 'DBI:Pg:database=mail;host=192.168.12.26'
2016-05-12T01:43:05.444339+02:00 mail3 amavis-prequeue[16363]:(16363-01)
connect_to_sql: 'DBI:Pg:database=mail;host=192.168.12.26' succeeded
2016-05-12T01:43:05.444908+02:00 mail3 amavis-prequeue[16363]:(16363-01)
sql: preparing and executing (6 args): SELECT * FROM
amavisd_select_policy WHERE policy_bank='IN_PRE_QUEUE' AND users.email
IN (?,?,?,?,?,?)
2016-05-12T01:43:05.447226+02:00 mail3 amavis-prequeue[16363]:(16363-01)
disconnecting from SQL
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., ,
2016-05-12T01:43:05.447905+02:00 mail3 amavis-prequeue[16363]:(16363-01)
(!!)TROUBLE in process_request: 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. at (eval 95) line 330.
2016-05-12T01:43:05.448097+02:00 mail3 amavis-prequeue[16363]:(16363-01)
(!)Requesting process rundown after fatal error

Hope, someone can help me?

Best regards
Daniel

[Tilman Schmidt]

Post by Daniel Funke
$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)" },
}

[...]

Post by Daniel Funke
2016-05-12T01:43:05.447732+02:00 mail3 amavis-prequeue[16363]:(16363-01)
...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