On Nov 4, 2008, at 9:21 AM, Webb Sprague wrote:
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.
If they're that smart, they're smart enough to deal with SQL, and
likely to be frustrated by a like-sql-but-not command language or
a GUI query designer.
Instead, create a user that only has enough access to read data (and
maybe create temporary tables) and use that user to give them
a sql commandline.
It'll be drastically less development effort for you, and the end result
is less likely to frustrate your users.
When I've done this I've also provided some useful plpgsql and sql
functions for users to use, to wrap commonly needed transformations,
and some views to hide parts of the data model they didn't need
to know about.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
