Postgres Pro
Downloads
Home   >   mailing lists

Thread: POSIX-style regular expressions

POSIX-style regular expressions

From
"Goran Buzic"
Date:
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,
CONSTRAINTprimary_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.

Re: POSIX-style regular expressions

From
Josh Jore
Date:
On Tue, 10 Sep 2002, Goran Buzic wrote:

>     id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),

> 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.

You should probably test it against PostgreSQL's regex engine. What you
may not know is that they all have different syntaxes, rules and quirks.
What works in one may or may not work in another.

So check out src/backend/regex and build retest (I think that's what it
was called). It's a command line regex tester (obviously against
PostgreSQL's implementation).

Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org

Re: POSIX-style regular expressions

From
Stephan Szabo
Date:
On Tue, 10 Sep 2002, Goran Buzic wrote:

> 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}$'),

Bad column type choice.  char(6) is space padded, so 1.2. looks like
"1.2.  ".  For me, 12.12. works, but any shorter one fails.  Unless
you need space padding elsewhere, you may just want to use varchar(6);

Re: POSIX-style regular expressions

From
"Ross J. Reedstrom"
Date:
On Tue, Sep 10, 2002 at 08:35:27AM -0500, Josh Jore wrote:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
> 
> >     id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> 
> > 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.
> 
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
> 
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).

Or, test directly in psql. I dropped your test data into a table, and
played with select:

test=# select * from testtable ;  id     --------   1.2.      1.12.      12.1.       12.12.      (4 rows)

test=# select * from testtable ;  id   
--------1.2.  1.12. 12.1. 12.12.
(4 rows)

test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2}$';  id   
--------12.12.
(1 row)

Hmm, that's because you said char(6), which is bank padded:

test=# select * from testtable where id ~* '^([0-9]{1,2}\.){2} *';  id   
--------1.2.  1.12. 12.1. 12.12.
(4 rows)

Further testing with your actual table def (what version are you using?
I dont have ON INSERT CASCADE in my 7.2.1 test database) indicates you
need to double up the slashes on the '.', as so:

'^([0-9]{1,2}\\.){2}$'

One set of slashes gets stripped by the command processor.

Note that this _still_ requires a 6 char input, so 1.2. fails, but
01.02. works.

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

Re: POSIX-style regular expressions

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>> id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),

> Bad column type choice.  char(6) is space padded, so 1.2. looks like
> "1.2.  ".  For me, 12.12. works, but any shorter one fails.  Unless
> you need space padding elsewhere, you may just want to use varchar(6);

Also, the backslash has to be doubled to get through the string literal
parser, so what you really want is

id1    varchar(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\\.){2}$'),

else it will accept values you don't want...
        regards, tom lane

Rules and Triggers

From
"Michael Paesold"
Date:
Hi all,

can you tell me in what order rules and triggers are executed?

First, what comes first, the rules, or the triggers?
And then, in what order are all the rules / triggers executed?

Regards,
Michael Paesold

Re: Rules and Triggers

From
"Adam Erickson"
Date:
Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
Triggers are carried out after the SQL is executed and the data is modified.
So, to answer your question, I think rules come first.  As to which order
the rules / triggers are executed probably depends on the order you put them
in when you created them but I pulled that straight out of my arse.  :)

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Michael Paesold
> Sent: Tuesday, September 10, 2002 1:53 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Rules and Triggers
>
>
> Hi all,
>
> can you tell me in what order rules and triggers are executed?
>
> First, what comes first, the rules, or the triggers?
> And then, in what order are all the rules / triggers executed?
>
> Regards,
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: Rules and Triggers

From
"Michael Paesold"
Date:
Adam Erickson wrote:

> Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> Triggers are carried out after the SQL is executed and the data is
modified.
> So, to answer your question, I think rules come first.  As to which order
> the rules / triggers are executed probably depends on the order you put
them
> in when you created them but I pulled that straight out of my arse.  :)

That would be correct for triggers with AFTER INSERT/UPDATE/DELETE.
But what about triggers BEFORE INSERT/UPDATE/DELETE?

Regards,
Michael

Re: Rules and Triggers

From
"Ross J. Reedstrom"
Date:
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
> 
> Adam Erickson wrote:
> 
> > Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> > Triggers are carried out after the SQL is executed and the data is
> modified.
> > So, to answer your question, I think rules come first.  As to which order
> > the rules / triggers are executed probably depends on the order you put
> them
> > in when you created them but I pulled that straight out of my arse.  :)
> 
> That would be correct for triggers with AFTER INSERT/UPDATE/DELETE.
> But what about triggers BEFORE INSERT/UPDATE/DELETE?

An important thing to remember about rules vs. triggers: Rules operate
on an SQL _query_ that involves the table (or view) that the rule is
attached to, whereas triggers operate on the individual _tuples_ of the
table they are attached to. Rules act to rewrite the incoming query,
at the very earliest step: just after parsing, but before planning and
optimizing. So rules _always_ act first. Triggers act when storage gets
hit: when the optimizer has choosen a plan, and passes it to the executor
for, well, execution.

Ross

Re: Rules and Triggers

From
"Michael Paesold"
Date:
Ross J. Reedstrom wrote:

> An important thing to remember about rules vs. triggers: Rules operate
> on an SQL _query_ that involves the table (or view) that the rule is
> attached to, whereas triggers operate on the individual _tuples_ of the
> table they are attached to. Rules act to rewrite the incoming query,
> at the very earliest step: just after parsing, but before planning and
> optimizing. So rules _always_ act first. Triggers act when storage gets
> hit: when the optimizer has choosen a plan, and passes it to the executor
> for, well, execution.
> 
> Ross

Thanks alot, again I understand a little bit more.

You're great, guys!

Regards, Michael Paesold

Re: Rules and Triggers: another question

From
"Michael Paesold"
Date:
Jerome Chochon wrote:

> Thanks for your answer but i have another question.
> Which one is the faster ?
> 
> If i write this trigger...
> CREATE TRIGGER trigger_name
> BEFORE DELETE
> ON table_name
> FOR EACH ROW EXECUTE PROCEDURE function();
> 
> and this rule:
> CREATE RULE name_rule AS
> ON DELETE TO table_name
> DO select function();
> 
> When i delete values, whichone will call the value the first ?
> 
> Someone can help me ??

With the RULE, the function will be called earlier than with the TRIGGER.
(according to what Ross said before)

Regards,
Michael

Re: POSIX-style regular expressions

From
"Goran Buzic"
Date:
I tried to build retest but there was a lot of massages like

...... undefined referenc to ..

and building regex tester failed with messages

collect2: ld returned 1 exit status
make: *** [retest] Error 1


"Josh Jore" <josh@greentechnologist.org> wrote in message
news:Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org...
> On Tue, 10 Sep 2002, Goran Buzic wrote:
>
> >     id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
>
> > 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.
>
> You should probably test it against PostgreSQL's regex engine. What you
> may not know is that they all have different syntaxes, rules and quirks.
> What works in one may or may not work in another.
>
> So check out src/backend/regex and build retest (I think that's what it
> was called). It's a command line regex tester (obviously against
> PostgreSQL's implementation).
>
> Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: POSIX-style regular expressions

From
"Goran Buzic"
Date:
Hi

I would like to thank you for helping me with regular expressions. Yours
suggestions to use VARCHAR and '\\.' pattern helped me a lot.

Best regards
Goran

Re: POSIX-style regular expressions

From
Bruce Momjian
Date:
I have commented out the Makefile entry for retest because I can't get
it to compile anymore.  Someone can come along and clean it up someday.

# retest will not compile because multibyte is now enabled by default
# and the multibyte calls require /mmgr, /adt, and other calls that
# are complex for linkage,  bjm 2002-09-16
#retest: retest.o SUBSYS.o $(DEBUGOBJ)
#       $(CC) $(CFLAGS) $(LDFLAGS) $^ $(LIBS) -o $@

---------------------------------------------------------------------------

Goran Buzic wrote:
> I tried to build retest but there was a lot of massages like
> 
> ...... undefined referenc to ..
> 
> and building regex tester failed with messages
> 
> collect2: ld returned 1 exit status
> make: *** [retest] Error 1
> 
> 
> "Josh Jore" <josh@greentechnologist.org> wrote in message
> news:Pine.BSO.4.44.0209100754190.3512-100000@kitten.greentechnologist.org...
> > On Tue, 10 Sep 2002, Goran Buzic wrote:
> >
> > >     id1    char(6) NOT NULL CHECK(id1 ~* '^([0-9]{1,2}\.){2}$'),
> >
> > > 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.
> >
> > You should probably test it against PostgreSQL's regex engine. What you
> > may not know is that they all have different syntaxes, rules and quirks.
> > What works in one may or may not work in another.
> >
> > So check out src/backend/regex and build retest (I think that's what it
> > was called). It's a command line regex tester (obviously against
> > PostgreSQL's implementation).
> >
> > Joshua b. Jore -{ weird geeky madness }-> http://www.greentechnologist.org
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073