Hi all,
I am writing an application that allows users to analyze demographic
and economic data, and I would like the users to be able to pick
columns, transform columns with functions (economists take the
logarithm of everything), and write customized WHERE and GROUP-BY
clauses. This is kind of like passing through a query to the DB in a
library catalog.
Has anybody found a good way to do this, especially inside the
database from a plpgsql function (select * from custom_query('table1',
'col1 > 100')) ? I don't want to just concatenate a user supplied
WHERE clause, at least without somehow checking the resulting
statement for (1) only one statement, (2) no data modification
clauses, and (3) only one "level" in the tree.
It seems like if I could interact with an SQL parser through a script,
I could accomplish this relatively easily. Perhaps SPI can help me
(give me hints!), though I don't really want to write any C. Perhaps
I am wrong about the possibility of this at all.
I realize that roles and permissions can help protect the system, but
I still feel nervous.
Has anybody done a similar thing, or tried? The problem is that if we
try to parameterize everything, then we don't really allow the kind of
data exploration that we are shooting for and these guys / gals are
smart enough to deal with a little syntax.
Thanks!
-W