On Mar 15, 2007, at 11:31 , Ron Mayer wrote:
Josh Berkus wrote:
And then what? dynamically construct all your SQL queries?
Sure, sounds like a simple solution to me...
Not to mention DB security issues. How do you secure your
database when
your web client has DDL access?
So, Edward, the really *interesting* idea would be to come up with a
secure, normalized way to do UDFs *without* EAV tables. People
would be
very impressed.
I have a system with many essentially user-defined fields, and was
thinking of creating something similar to an Array type and writing
some GIST indexes for it.
My current workaround is to store them as a YAML document and use
tsearch to index it (with application logic to further refine the
results) - but a EAV datatype that could be put in tables and
effectively indexed would be of quite a bit of interest here.
And yes, a better say to do UDFs would be even cooler.
Out of all the databases that I have used, postgresql offers the most
flexible DDL- mostly for one reason: they can operate within
transactions.
To handle arbitrary strings as column identifiers, the column names
could actually be stripped down to lower-case letters and the "real
title" could be stored in a separate table or as column comments.
Mr. Berkus' concern regarding the security implications is already
handled by privilege separation or security-definer functions.
The OP's concern about the difficulty about querying a schema
structure is alleviated via any number of APIs in Perl, JDBC, etc.
It seems to me that postgresql is especially well-suited to run DDL
at runtime, so what's the issue?
-M
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org