Обсуждение: named generic constraints [feature request]
So last time I checked this wasn't possible (at least not that anyone has told me). I'd like to be able to create constraints that aren't tied to a specific table/column. I think that the syntax would look something like this CREATE CONSTRAINT empty CHECK (VALUE = '\0' ); this should allow us to do thinks like CREATE TABLE users ( username TEXT NOT empty ); the example from create domain (modified) is also pretty good CREATE CONSTRAINT zip CHECK( VALUE ~ '^\\d{5}$' OR VALUE ~ '^\\d{5}-\\d{4}$' ); -- Caleb Cushing http://xenoterracide.blogspot.com
Hello do you know domains? It is very similar to your proposal. http://www.postgresql.org/docs/8.2/static/sql-createdomain.html Regards Pavel Stehule 2009/11/23 Caleb Cushing <xenoterracide@gmail.com>: > So last time I checked this wasn't possible (at least not that anyone > has told me). I'd like to be able to create constraints that aren't > tied to a specific table/column. > > I think that the syntax would look something like this > > CREATE CONSTRAINT empty CHECK (VALUE = '\0' ); > > this should allow us to do thinks like > > CREATE TABLE users ( > username TEXT NOT empty > ); > constraint cannot be part of expression. CREATE OR REPLACE FUNCTION emptystr(text) RETURNS bool AS $$ SELECT $1 <> ''; -- it is SQL not C $$ LANGUAGE sql; CREATE TABLE users( username TEXT CHECK (NOT emptystr(username)), ... p.s. Is it related to ANSI SQL? Regards Pavel Stehule > the example from create domain (modified) is also pretty good > > CREATE CONSTRAINT zip CHECK( > VALUE ~ '^\\d{5}$' > OR VALUE ~ '^\\d{5}-\\d{4}$' > ); > > -- > Caleb Cushing > > http://xenoterracide.blogspot.com > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > do you know domains? It is very similar to your proposal. > obviously since I cited it. > constraint cannot be part of expression. > why not? NOT NULL is a contraint, UNIQUE is a contstraint. > CREATE OR REPLACE FUNCTION emptystr(text) > RETURNS bool AS $$ > SELECT $1 <> ''; -- it is SQL not C > $$ LANGUAGE sql; > > CREATE TABLE users( > username TEXT CHECK (NOT emptystr(username)), > ... this is probably the 'best' current solution. however, I'd like to be able to not have to name the column for every constraint. and domains only seem right if it's something, like a zip code, that has a very specific set of rules, that is in reality it's own type. where specifying something like 'empty' feels as generic (and arbitrary?) as null. empty is not the only example (I'm sure), just the best I can think of. > p.s. Is it related to ANSI SQL? not to my knowledge (can't say that it isn't though, I've never read the standard). -- Caleb Cushing http://xenoterracide.blogspot.com
2009/11/23 Caleb Cushing <xenoterracide@gmail.com>: > On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Hello >> >> do you know domains? It is very similar to your proposal. >> > > obviously since I cited it. > >> constraint cannot be part of expression. >> > why not? NOT NULL is a contraint, UNIQUE is a contstraint. yes - but you are defined constraint empty - not "not empty". for example - there are not a constraint "NOT UNIQUE". I thing, so this isn't workable. Constrainst are hard coded - it uses keywords. Your new syntax is redundant - there are not any special value to using CHECK clause and functions. Regards Pavel Stehule > >> CREATE OR REPLACE FUNCTION emptystr(text) >> RETURNS bool AS $$ >> SELECT $1 <> ''; -- it is SQL not C >> $$ LANGUAGE sql; >> >> CREATE TABLE users( >> username TEXT CHECK (NOT emptystr(username)), >> ... > > this is probably the 'best' current solution. however, I'd like to be > able to not have to name the column for every constraint. and domains > only seem right if it's something, like a zip code, that has a very > specific set of rules, that is in reality it's own type. where > specifying something like 'empty' feels as generic (and arbitrary?) as > null. empty is not the only example (I'm sure), just the best I can > think of. > >> p.s. Is it related to ANSI SQL? > > not to my knowledge (can't say that it isn't though, I've never read > the standard). > -- > Caleb Cushing > > http://xenoterracide.blogspot.com >
On mån, 2009-11-23 at 12:50 -0500, Caleb Cushing wrote: > and domains > only seem right if it's something, like a zip code, that has a very > specific set of rules, that is in reality it's own type. A domain is not really its own type, it's a domain over its base type. Hence the name. > where > specifying something like 'empty' feels as generic (and arbitrary?) as > null. The problem with your empty constraint is that it's data type specific, and therefore the operator is also different depending on context. So either you create a "named generic constraint" for every data type you are interested in (in that case, see domains), or the thing could at best work as a text substitution mechanism, which is something that SQL typically doesn't do. > empty is not the only example (I'm sure), just the best I can > think of. I doubt that there are any really good examples that cannot be solved with the current facilities.
> CREATE OR REPLACE FUNCTION emptystr(text) > RETURNS bool AS $$ > SELECT $1 <> ''; -- it is SQL not C > $$ LANGUAGE sql; > > CREATE TABLE users( > username TEXT CHECK (NOT emptystr(username)), although I'm not going to continue discussing the request. this code as the opposite desired effect. it should be SELECT $1 = ''; -- you have a double negative -- Caleb Cushing http://xenoterracide.blogspot.com
Caleb, I can understand why you want this. However, it would be tricky to implement because of data typing, and is fairly easily worked around using either domains or functions. So I don't think anyone is going to want to add it to the TODO list, sorry. Of course, Postgres is fully hackable if you *really* want it. --Josh Berkus
2009/11/24 Caleb Cushing <xenoterracide@gmail.com>: >> CREATE OR REPLACE FUNCTION emptystr(text) >> RETURNS bool AS $$ >> SELECT $1 <> ''; -- it is SQL not C >> $$ LANGUAGE sql; >> >> CREATE TABLE users( >> username TEXT CHECK (NOT emptystr(username)), > > although I'm not going to continue discussing the request. this code > as the opposite desired effect. it should be > > SELECT $1 = ''; -- you have a double negative no - "--" is line comment in SQL - it same like "//" in C++ Regards Pavel Stehule > > -- > Caleb Cushing > > http://xenoterracide.blogspot.com >
> no - > > "--" is line comment in SQL - it same like "//" in C++ sorry didn't see this was updated. I know -- is a comment I mean in sql <> means NOT your function name is emptystr which implies it looks for an emptystr and returns true if the string is found to be empty (at least in my mind). so if you want to create a contrstraint of not empty you'd write NOT emptystr(col) however the way you wrote it would only return true if the string was NOT <> empty which is a double negative meaning that it is empty thereby rejecting all but empty strings. my final function that I wrote ended up looking like this (note: I didn't specify to include whitespace in my original explanation. CREATE OR REPLACE FUNCTION empty(TEXT) RETURNS bool AS $$SELECT $1 ~ '^[[:space:]]*$'; $$ LANGUAGE sql IMMUTABLE; COMMENT ON FUNCTION empty(TEXT) IS 'Find empty strings or strings containing only whitespace'; which I'm using like this (note: this is not the full table) CREATE TABLE users ( user_name TEXT NOT NULL UNIQUE CHECK ( NOT empty( user_name )) ); I still wish I could write,something like CREATE CONSTRAINT empty CHECK ( VALUE NOT ~ '^[[:space:]]*$';) CREATE TABLE users ( user_name TEXT NOT NULL UNIQUE CHECK ( NOT empty ) );CREATE TABLE roles ( role_name TEXT NOT NULL UNIQUE CHECK ( NOT empty) ); -- Caleb Cushing http://xenoterracide.blogspot.com
2009/12/7 Caleb Cushing <xenoterracide@gmail.com>: >> no - >> >> "--" is line comment in SQL - it same like "//" in C++ > > sorry didn't see this was updated. I know -- is a comment > > I mean in sql <> means NOT your function name is emptystr which > implies it looks for an emptystr and returns true if the string is > found to be empty (at least in my mind). so if you want to create a > contrstraint of not empty you'd write NOT emptystr(col) however the > way you wrote it would only return true if the string was NOT <> empty > which is a double negative meaning that it is empty thereby rejecting > all but empty strings. > > my final function that I wrote ended up looking like this (note: I > didn't specify to include whitespace in my original explanation. > > > > > CREATE OR REPLACE FUNCTION empty(TEXT) > RETURNS bool AS $$ > SELECT $1 ~ '^[[:space:]]*$'; > $$ LANGUAGE sql > IMMUTABLE; > COMMENT ON FUNCTION empty(TEXT) > IS 'Find empty strings or strings containing only whitespace'; > > which I'm using like this (note: this is not the full table) > > CREATE TABLE users ( > user_name TEXT NOT NULL > UNIQUE > CHECK ( NOT empty( user_name )) > ); > > I still wish I could write,something like > > CREATE CONSTRAINT empty CHECK ( VALUE NOT ~ '^[[:space:]]*$';) > > CREATE TABLE users ( > user_name TEXT NOT NULL > UNIQUE > CHECK ( NOT empty ) > ); > CREATE TABLE roles ( > role_name TEXT NOT NULL > UNIQUE > CHECK ( NOT empty) I understand. But I don't see any significant benefit for this non-standard feature. You safe a few chars. I thing so it is useless. Regards Pavel Stehule > ); > -- > Caleb Cushing > > http://xenoterracide.blogspot.com >