From: | Ben Clewett <B(dot)Clewett(at)roadrunner(dot)uk(dot)com> |
---|---|
To: | |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: CHECK constraint |
Date: | 2003-03-13 16:26:15 |
Message-ID: | 3E70B127.5000201@roadrunner.uk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Just a final comment...
You return a 'bool' from your funtion by virtue that the one and only
value found is a 'bool'??
If the query retures no, more than one, or a mixture of 'true' and
'false' the result might be unpredictable... If I understand the syntax.
Would it be safer to use:
CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bigint AS'
SELECT count(*) FROM bar WHERE id = $1 AND live = 't'
' language 'sql' STABLE STRICT;
CREATE TABLE foo (
id int4 NOT NULL CHECK (check_bar(id) != 0)
);
???
Ben
Joe Conway wrote:
> Ben Clewett wrote:
>
>> Is this possible?? Should I cut-and-run here and do the coding in
>> application space?
>
>
> How 'bout:
>
> CREATE TABLE bar (
> id int4 NOT NULL,
> live bool
> );
>
> insert into bar values(1,'t');
> insert into bar values(2,'f');
>
> CREATE OR REPLACE FUNCTION check_bar(int4) RETURNS bool AS'
> SELECT live FROM bar WHERE id = $1
> ' language 'sql' STABLE STRICT;
>
> CREATE TABLE foo (
> id int4 NOT NULL CHECK (check_bar(id))
> );
>
> regression=# insert into foo values(1);
> INSERT 1336840 1
> regression=# insert into foo values(2);
> ERROR: ExecInsert: rejected due to CHECK constraint "foo_id" on "foo"
>
> HTH,
>
> Joe
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Earl | 2003-03-13 18:08:50 | Re: Version Number |
Previous Message | paul butler | 2003-03-13 16:21:04 | Re: Getting Started!? |