Re: [SQL] Finding broken regex'es
On 10/2/07, Enrico Weigelt <[EMAIL PROTECTED]> wrote:
>
> Hi folks,
>
>
> I'm looking for some way to find broken regex'es in some column
> to kick them off. For now I'm regularily fetching all regexes
> from an PHP script, try an preg_match() and so find the broken
> ones to later remove them.
>
> Is there any way to do this directly within the db ?
Of course. Exceptions is what You need!
CREATE FUNCTION regex_is_broken(r text) RETURNS boolean AS $$
BEGIN
PERFORM '' ~ r;
RETURN 'f';
EXCEPTION
WHEN INVALID_REGULAR_EXPRESSION THEN
RETURN 't';
END;
$$ LANGUAGE PLpgSQL STRICT IMMUTABLE;
...and then you could do something like:
DELETE FROM table WHERE regex_is_broken(rx_col);
You don't need PLpgSQL to prevent such invalid regexes in the
first place. You could use CHECK constraint for it:
CREATE TABLE rx_check (
rx text CHECK ('' ~ rx IN ('t','f'))
);
postgres=> INSERT INTO rx_check (rx) VALUES ('.*');
INSERT 0 1
Time: 13.660 ms
postgres=> INSERT INTO rx_check (rx) VALUES ('234234');
INSERT 0 1
Time: 2.282 ms
postgres=> INSERT INTO rx_check (rx) VALUES ('par).*');
ERROR: invalid regular expression: parentheses () not balanced
Regards,
Dawid
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Finding broken regex'es
2007/10/3, Dawid Kuroczko <[EMAIL PROTECTED]>:
> CREATE TABLE rx_check (
> rx text CHECK ('' ~ rx IN ('t','f'))
> );
wow. This is beautiful :)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Finding broken regex'es
On 10/3/07, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote:
> 2007/10/3, Dawid Kuroczko <[EMAIL PROTECTED]>:
>
> > CREATE TABLE rx_check (
> > rx text CHECK ('' ~ rx IN ('t','f'))
> > );
>
> wow. This is beautiful :)
Personally I would wrap it around DOMAIN, i.e.:
CREATE DOMAIN regex AS
text CHECK ('' ~ VALUE IN (TRUE,FALSE));
And then use 'regex' type instead of 'text'. For a nice look&feel:
CREATE TABLE rx_test (
rx regex
);
qnex=>insert into rx_test values ('.*');
INSERT 0 1
qnex=>insert into rx_test values ('qwe');
INSERT 0 1
qnex=>insert into rx_test values ('aaa(aaa');
ERROR: invalid regular expression: parentheses () not balanced
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Need help with CASE statement in Function
Dear friends, I am a new user to postgreSQL and really need help to solve my "stupid ?" problem. I have created function with 4 arguments like this : CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ select * from tbltrans where tbltrans."Date" between $1 and $2 and upper(tbltrans."ProductID")=upper($3) and tbltrans."StoreID"=$4 order by tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; - My Question is : How to make argument 4 optional ? When IS NULL the function will show all transaction between date $1 and $2 and product ID=$3 But when $4 is not null then the fucntion will show all transaction between date $1 and $2 and product ID=$3 AND STOREID=$4. I really appreciate any suggestions. Thanks a lot. Hengky
Re: [SQL] Need help with CASE statement in Function
On 10/3/07, Hengky Lie <[EMAIL PROTECTED]> wrote: > Dear friends, > I am a new user to postgreSQL and really need help to solve my "stupid ?" > problem. > > I have created function with 4 arguments like this : > > CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate > date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" > AS > > $body$ > /* New function body */ > select * from tbltrans > where tbltrans."Date" between $1 and $2 > and upper(tbltrans."ProductID")=upper($3) > and tbltrans."StoreID"=$4 > order by tbltrans."Tanggal"; > $body$ > > LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; > - > My Question is : How to make argument 4 optional ? When IS NULL the function > will show all transaction between date $1 and $2 and product ID=$3 > But when $4 is not null then the fucntion will show all transaction between > date $1 and $2 and product ID=$3 AND STOREID=$4. Try something like: CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ SELECT * FROM tbltrans WHERE tbltrans."Date" between $1 and $2 AND upper(tbltrans."ProductID")=upper($3) AND ($4 IS NULL OR tbltrans."StoreID"=$4) ORDER BY tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; In other words, change: tbltrans."StoreID"=$4 into ($4 IS NULL OR tbltrans."StoreID"=$4) Haven't tested it, but should work fine. Regards, Dawid ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need help with CASE statement in Function
--- Hengky Lie <[EMAIL PROTECTED]> wrote: > My Question is : How to make argument 4 optional ? When IS NULL the function > will show all transaction between date $1 and $2 and product ID=$3 Could you simply overload your function by having two functions? One with arguement 4 and one without? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] field separator problem
Hi Everybody, I am having a problem with field separator. Maybe someone can assist me. But first thing firtst: I am running postgresql 8.2.4 on redhat, dell 64 bit machine: I issue from psql \f (or "\pset fieldsep ','") and psql appears to accept what I want: canon=# \f ',' Field separator is ",". canon is the name of database, but when I issue a select command, it still uses '|' as separator. What am I doing wront? Regards, Tena Sakai [EMAIL PROTECTED]
[SQL] Why does the sequence skip a number with generate_series?
I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); id | num +- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 6 7 | 7 8 | 8 9 | 9 10 | 10 12 | 11 13 | 12 14 | 13 15 | 14 16 | 15 17 | 16 18 | 17 19 | 18 20 | 19 21 | 20 23 | 21 24 | 22 25 | 23 26 | 24 27 | 25 28 | 26 29 | 27 30 | 28 31 | 29 32 | 30 But, if I just use single inserts, the sequence increments by one like I expect: jefftest=# INSERT INTO jefftest (num) VALUES (1); INSERT 0 1 jefftest=# INSERT INTO jefftest (num) VALUES (2); INSERT 0 1 jefftest=# INSERT INTO jefftest (num) VALUES (3); INSERT 0 1 jefftest=# INSERT INTO jefftest (num) VALUES (4); INSERT 0 1 jefftest=# INSERT INTO jefftest (num) VALUES (5); INSERT 0 1 jefftest=# select * from jefftest; id | num +- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 (5 rows) Obviously, this doesn't hurt anything, I'm just curious why it skips one after every generate_series insert? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] field separator problem
On Wed, Oct 03, 2007 at 05:13:48PM -0700, Tena Sakai wrote: > I issue from psql \f (or "\pset fieldsep ','") and > psql appears to accept what I want: > > canon=# \f ',' > Field separator is ",". > > canon is the name of database, but when I issue a select > command, it still uses '|' as separator. What am I > doing wront? fieldsep applies only to unaligned mode (\a or \pset format unaligned). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why does the sequence skip a number with generate_series?
On Tue, 2 Oct 2007, Jeff Frost wrote: > I expected these numbers to be in sync, but was suprised to see that the > sequence skips a values after every generate series. > > CREATE TABLE jefftest ( id serial, num int ); > INSERT INTO jefftest (num) values (generate_series(1,10)); > INSERT INTO jefftest (num) values (generate_series(11,20)); > INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] field separator problem
many thanks, Michael! I appreciate it. Regards, Tena -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Wed 10/3/2007 5:54 PM To: Tena Sakai Cc: [email protected] Subject: Re: [SQL] field separator problem On Wed, Oct 03, 2007 at 05:13:48PM -0700, Tena Sakai wrote: > I issue from psql \f (or "\pset fieldsep ','") and > psql appears to accept what I want: > > canon=# \f ',' > Field separator is ",". > > canon is the name of database, but when I issue a select > command, it still uses '|' as separator. What am I > doing wront? fieldsep applies only to unaligned mode (\a or \pset format unaligned). -- Michael Fuhr
