Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 75BBE47630C
	for <pgsql-sql@postgresql.org>; Tue, 10 Sep 2002 05:34:03 -0400 (EDT)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 0673F4762BC
	for <pgsql-sql@postgresql.org>; Tue, 10 Sep 2002 05:34:03 -0400 (EDT)
Received: by news.hub.org (Postfix, from userid 8)
	id 53D25381D46; Tue, 10 Sep 2002 05:34:02 -0400 (EDT)
From: "Goran Buzic" <goran.buzic@hep.hr>
X-Newsgroups: comp.databases.postgresql.sql
Subject: POSIX-style regular expressions
Date: Tue, 10 Sep 2002 11:32:51 +0200
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 35
Message-ID: <alkea9$104o$1@news.hub.org>
X-Complaints-To: news@hub.org
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/67
X-Sequence-Number: 9344

Hi

Can anybody help me with POSIX-style regular expression used to check rules
that new data must satisfy for an insert or update operetion.

Table was created as follows.

CREATE TABLE table_name1 (
    id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
    id2    INT NOT NULL,
    CONSTRAINT primary_key
        PRIMARY KEY (id1, id2),
    CONSTRAINT id2_exists
        FOREIGN KEY (id2) REFERENCES table_name2
        ON DELETE CASCADE
        ON INSERT CASCADE
);

Any id1 that looks like
    1.2.
    1.12.
  12.1.
  12.12.
should be inserted into table, but the insert query was rejected with an
error message

ERROR:    ExecAppend: rejected due to CHECK constraint table_name1_id1

I tested preceding regular expression with Perl and JavaScript and it worked
fine. Can I use regular expressions with CHECK parametar, and if so, how can
I make it work.

The platform PostgreSQL is installed is Solaris 8 - Intel.