agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Volkan YAZICI <yazicivo@ttnet.net.tr>
To: Nikolay Samokhvalov <nikolay@samokhvalov.com>
Cc: pgsql-sql@postgresql.org, Jeff Frost <jeff@frostconsultingllc.com>
Subject: Re: Check/unique constraint question
Date: Sun, 5 Mar 2006 12:05:27 +0200
Message-ID: <20060305100526.GA214@alamut> (raw)
In-Reply-To: <e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>
References: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>
	<e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>

On Mar 05 12:02, Nikolay Samokhvalov wrote:
> Unfortunately, at the moment Postgres doesn't support subqueries in
> CHECK constraints

I don't know how feasible this is but, it's possible to hide subqueries
that will be used in constraints in procedures. Here's an alternative
method to Nikolay's:

CREATE TABLE where_check (active bool, id int);

CREATE OR REPLACE FUNCTION check_id (bool, int) RETURNS bool AS '
  SELECT CASE
    WHEN $1 THEN NOT EXISTS (SELECT 1
                             FROM where_check AS W
                             WHERE W.active IS TRUE AND W.id = $2)
    ELSE TRUE
  END;
' LANGUAGE SQL;

-- A partial index like
-- CREATE INDEX active_id_idx ON where_check (id)
--   WHERE active IS TRUE;
-- should speed up above query

ALTER TABLE where_check ADD CONSTRAINT idchk
  CHECK (check_id(active, id));

test=# INSERT INTO where_check VALUES (TRUE, 2);
INSERT 0 1
test=# INSERT INTO where_check VALUES (FALSE, 2);
INSERT 0 1
test=# INSERT INTO where_check VALUES (TRUE, 2);
ERROR:  new row for relation "where_check" violates check constraint
"idchk"


Regards.

view thread (11+ messages)  latest in thread

Message-ID: <20060305100526.GA214@alamut>
Permalink:  ../20060305100526.GA214@alamut/
Also on:    postgresql.org/message-id/20060305100526.GA214@alamut

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: yazicivo@ttnet.net.tr, nikolay@samokhvalov.com, jeff@frostconsultingllc.com
  Subject: Re: Check/unique constraint question
  In-Reply-To: <20060305100526.GA214@alamut>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox