Sam, Thanks for thinking along.
The thing is that a SINGLE constraint might apply to MULTIPLE fields; therefore it seems best to build a set of key/value pairs... Multiple doesComply()s won't do the job :(
BY THE WAY:
I came to think of another option: putting additional columns (that is: addittional to the default set of fields) in xml, in a column that is part of row (=object) it belongs to.
Any body has done so before? Any body has experience with XML schema validation within PostgreSQL?
Cheerz,
Rob
2009/9/24 Sam Mason
<sam@samason.me.uk>On Thu, Sep 24, 2009 at 09:23:35PM +0200, Rob Marjot wrote:
> SELECT doesComply('relationname', keyValues.*) FROM (VALUES('col1',
> CAST(col1 AS TEXT)), VALUES('col2', CAST(col2 AS TEXT))) AS
> keyValues(the_key, the_value);
>
> The function "doesComply()" will then process the CONSTRAINTS table and
> raise an Error if the new / updated row does not fit...
I'd have a set of doesComply functions, the first two parameters
as you have them but overload a set to support different datatypes
specifically. Something like:
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val INT) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val DATE) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val TEXT) ...
CREATE FUNCTION doesComply(_rel TEXT, _key TEXT, _val NUMERIC) ...
And then have a set of "attribute" tables (one for each datatype) to
store the actual values in. At least PG can do some type checking for
you that way. Either that, or just leave them all as text to text
mappings in the database and only attempt to type things out in the
client code.
Not sure why you're doing the VALUES contortions as well, why not just:
SELECT doesComply('relationname', 'col1', col2);