Re: [HACKERS] named generic constraints [feature request]
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 TEXTNOT 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 TEXTNOT NULL UNIQUE CHECK ( NOT empty ) ); CREATE TABLE roles ( role_name TEXTNOT NULL UNIQUE CHECK ( NOT empty) ); -- 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
Re: [HACKERS] named generic constraints [feature request]
2009/12/7 Caleb Cushing xenoterrac...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
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 xenoterrac...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule pavel.steh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
2009/11/23 Caleb Cushing xenoterrac...@gmail.com: On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule pavel.steh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
2009/11/24 Caleb Cushing xenoterrac...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
