Обсуждение: POSIX-style regular expressions
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.
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
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);
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
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
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
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
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
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
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
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
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
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
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