[SQL] Regexps -- too complex?
Running 7.0.2 on Alpha/RedHat 6.2 256MB RAM In order to implement a fulltext search, I have a func that parses list of words and creates a regexp query with things like [[:<:]]( word | word | ... )[[:>:]] That query then is passed to backend... Now the strange thing: gamenet=# SELECT id, title,publishdate,categoryid FROM articles WHERE translate(title,'abcdefghijklmnopqrstuvwxyzâèçìîíïòðûþõäöü','ABCDEFGHIJKLMNOPQRSTUVWXYZÂÈÇÌÎÍÏÒÐÛÞÕÄÖÜ') ~ '(BLACK|SERIOUS|SAM)[[:>:]]'::text ; id | title | publishdate | categoryid --+-+-+ 600 | Serious Sam ceïâ pie pircçjiem | 2001-03-22 |149 523 | Black & White gaidîðanas svçtki | 2001-03-19 |155 241 | Lorgaine: The Black Standard - íeltu varoòeposs | 2001-02-27 |155 707 | Lorgaine: The Black Standard beta versija | 2001-03-23 |156 1484 | Black&White tomçr neesot spiegu programma | 2001-04-18 |155 1490 | Black & White FAQ | 2001-04-18 |160 1496 | Black & White "ïaunais" FAQ | 2001-04-18 |160 1732 | Black & White - pârdotâkâ spçle ASV | 2001-04-24 |155 (8 rows) gamenet=# SELECT id, title,publishdate,categoryid FROM articles WHERE translate(title,'abcdefghijklmnopqrstuvwxyzâèçìîíïòðûþõäöü','ABCDEFGHIJKLMNOPQRSTUVWXYZÂÈÇÌÎÍÏÒÐÛÞÕÄÖÜ') ~ '(BLACK|SERIOUS|WHITE|SAM)[[:>:]]'::text ; id | title | publishdate | categoryid +---+-+ (0 rows) It seems that if the regexp is too complex (more than 3 |-ed elements) it doesnt return. Any ideas? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] using top-level aggregate values in subqueries
Subject: Re: [SQL] using top-level aggregate values in subqueries Date: Mon, 23 Apr 2001 23:24:48 -0500 From: Ossie J. H. Moore <[EMAIL PROTECTED]> To: "Thomas F. O'Connell" <[EMAIL PROTECTED]> I'm a little unclear on what you are trying to do here so I'll take a stab at explaining how you can compare two or more columns to the same columns in a sub query... 1. Let's assume you have three tables: CUSTomers (cust_id, cust_name), MAGazines (mag_id, mag_name), TERMs (cust_id, mag_id, mag_expire) where expire is an int2 representing the year. Values in the tables are as follows: CUST: 1, JACK; 2, JILL; 3, JOE MAG: 1, DOGS; 2, CATS TERM: 1,1,2000; 1,1,2001; -- JACK subscribed to DOGS in 2000,2001 2,1,2000; -- JILL subscribed to DOGS in 2000 2,2,2001; -- JILL subscribed to CATS in 2001 3,2,2000; 3,2,2001; -- JOE subscribed to CATS IN 2000,2001 2. You want to see a list of customer and magazine name pairs of those expiring in 2001. SELECT C.CUST_NAME, M.MAG_NAME FROM CUST C, MAG M WHERE (C.CUST_ID, M.MAG_ID) IN ( SELECT T.CUST_ID, T.MAG_ID FROM TERM T WHERE T.TERM_EXPIRE = 2001 ) ORDER BY C.CUST_NAME, M.MAG_NAME 3. For this specific situation, the better solution might have been the statement below but the intent was to show how to match multiple columns in your parent query to multiple columns in a sub query. selectc.cust_name , m.mag_name from cust c , mag m , term t where c.cust_id = t.cust_id and m.mag_id = t.mag_id and t.term_expire = 2001; On Monday 23 April 2001 19:26, you wrote: > from the docs, i know that if you have two tables, foo and bar, you can > write a query such as > > select f.bling > from foo f > where f.id = ( > select max( b.id ) > from bar b > where b.bling = "i kiss you!" > ); > > what i'm wondering is if you need that subquery in two places in a query > if there's some way to cache it at the top level. > > for instance, if i were shooting for > > select f.id > from foo f, ola o > where f.id = ( > select max( b.id ) > from bar b > where b.bling = "i kiss you!" > ) > and o.id != ( > select max( b.id ) > from bar b > where b.bling = "i kiss you!" > ) > > is there some way to grab the value returned by the subquery in the > superquery and use the value instead of running the subquery twice? > > i'm not looking for an optimized version of my example (unless it > answers the question of the bigger picture); i'd rather know if there's > some way to access top-level aggregates from within a subquery. > > or find out that postgres is smart enough to recognize bits of SQL in a > query that are identical and do its own internal caching. > > generically stated, my question is: > > is there some way, without writing a function, to calculate an aggregate > value in a query that is used in multiple subqueries without needing to > run an aggregating query multiple times? > > i know it only amounts to syntactic sugar, but, as such, it would be > pretty sweet. > > thanks. > > -tfo > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Using Random Sequence as Key
Hello Bernardo, there is a somewhat mathematical solution to your problem. You can use a unique key, say 0<= k < MAX for each record, and a second key which is computed from the first, say f(k), which will give you a unique permutation of the numbers 0,...,MAX-1. It will not be random, but it will be hard for someone to figure out the original unique key k, without knowning your method. Here is one way to compute f(k): (math follows, beware!) The Fibonacci numbers are a sequence defined as follows: F(0) = 0, F(1) = 1, F(n+2) = F(n+1)+F(n) (for n>=0). Now, for every fixed number N>=2, you can define a permutation f(k) of the numbers 0,...,F(N)-1 as follows: f(k) = (k*F(N-1)) mod F(N) For example, if N=6, we have F(6)=8 and F(5)=5, so for 0<= k <8, f(k) = 5*k mod 8 is a permutation. In particular, k= 0 1 2 3 4 5 6 7 f(k) = 0 5 2 7 4 1 6 3 So, since you need approximately 9 numbers (1 to 9), you can use N=25, which will give you F(25)=75025 and F(24)=46368. Thus, you can have 75025 (slightly fewer than 9) unique values, by translating k into f(k) = 46368*k mod 75025 Unless your users know number theory, this should work for you. Vasilis Bernardo de Barros Franco <[EMAIL PROTECTED]> wrote: : Hello, I was wondering if noone can help me maybe someone could at least : give me some directions where to look for info or where to ask: : I wanted to index a table by a random key. Exemplifying, when a insert is : made, the id value is automatically filled with a random number between : 1 and 9. I'm pretty new in pgsql so I could use a howto or : something, and I repeat, if you don't know how, directions to where I can : find info on that or where I can ask someone that might know would be : enough. : Thank you ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] MySql 'REPLACE'
At 4/25/2001 07:38 AM, you wrote: I am working in porting the Slash software from MySql to PostgreSQL. I stepped in MySql's REPLACE command (a SQL command) that to my knowledge is not supported by PostgreSQL and it's not standard SQL. According to MySql's manual: "REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. See section 7.21 INSERT syntax." REPLACE INTO table (column, column...) VALUES (value, value...) Has anyone had any experience about how to simulate it under PostgreSQL? I am using Perl and I can move most of the thing into application anyway. You should be able to do this with two separate queries inside a transaction. The only part I'm not clear on is whether to use an 'and' or an 'or' on the delete. Check and see if all values have to match or if partial matches, i.e. only one of the columns, are acceptable. If it does partial matching, then use the 'or', otherwise use the 'and'. Give this a try... begin; delete from table where (column=value) and (column=value) and ...; insert into table (column, column...) values (value, value...); end; From what you've given me, I think this would emulate that behavior. Hope it helps...
[SQL] problem with pl/pgsql
hey guys,
I am writing an article about using Perl inside sql commands.
I am not having a problem with perl, but with pl/pgsql.
The documentation for pl/pgsql isn't helping me out, although I am sure
I will figure it out eventually.
Here is the perl function,
CREATE FUNCTION search_name(employee,text,integer) RETURNS text AS '
my $emp = shift;
my $Text = shift;
my $Case = shift;
if (($Case > 0) && ($emp->{''name''} =~ /\\Q$Text\\E/i))
{ return $emp->{''name''}; }
elsif ($Case > 0) {return "";}
elsif ($emp->{''name''} =~ /\\Q$Text\\E/)
{return $emp->{''name''}; }
else { return "";}
' LANGUAGE 'plperl';
insert into EMPLOYEE values ('John Doe',1,1);
insert into EMPLOYEE values ('Jane Doe',1,1);
select name,search_name(employee,'j',0) from employee;
select name,search_name(employee,'j',1) from employee;
select name from employee where search_name(employee,'j',1) = name;
select name from employee where search_name(employee,'j',0) = name;
I know these functions aren't elegant, but oh well.
Here isthe pl/pgsql
CREATE FUNCTION insert_name(text)
RETURNS integer AS '
DECLARE
rec1 record; text1 text;
BEGIN
text1 := $1;
SELECT INTO rec1 count(name)
FROM employee
where search_name(employee,text1,0) = name
limit 1;
IF rec1.count = 0
THEN insert into employee (name) values (text1);
return 1;
END IF;
return 0;
END;
' LANGUAGE 'plpgsql';
What I am trying to do is set something up where it will only
insert a value if it doesn't exist.
I want it to return either 0 or 1.
However, it returns 6 rows if there are 6 entries as 0 or 1.
In my perl statement, it doesn't return anythng if a row
doesn't match. I want the pl/pglsq statement to not return
anything for any rows whatsoever, and to return either a 1 or 0
at the very end of the function.
How do I execute sql commands inside pl/pgsql so that
the that they remain hidden?
I plan on using pl/pgsql a lot now. I really want to combine
perl and pl/pgsql as standard sql options aren't that great.
Thanks!
Mark
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SET SEQSCAN TO OFF - error
Hans-Jürgen Schönig writes: > I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > Does anybody know why? Because 'seqscan' is not a valid option name. Maybe you mean enable_seqscan. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SET SEQSCAN TO OFF - error
Hans-Jürgen Schönig wrote: > > I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > Does anybody know why? > because the documentation is wrong. the variable name is ENABLE_SEQSCAN, i.e.: SET ENABLE_SEQSCAN TO OFF; should work. -- Jeff Hoffmann PropertyKey.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SET SEQSCAN TO OFF - error
> I have the command below like it is described in the documentation > (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. > > SET SEQSCAN TO OFF; > ERROR: 'seqscan' is not a valid option name > > > Does anybody know why? > Try: test=> set enable_seqscan to off; SET VARIABLE -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] SET SEQSCAN TO OFF - error
I have the command below like it is described in the documentation (http://www.archonet.com/pgdocs/force-index.html) but it doens't work. SET SEQSCAN TO OFF; ERROR: 'seqscan' is not a valid option name Does anybody know why? Hans ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Regexps -- too complex?
"Emils Klotins" <[EMAIL PROTECTED]> writes: > Running 7.0.2 on Alpha/RedHat 6.2 256MB RAM Update to 7.1. 7.0.* has a lot of portability problems on Alphas, and one of them is that regexps with between 33 and 64 states don't work (int vs long problem...) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] problem with pl/pgsql
Mark, I'd love to help you with your PL/pgSQL function, but since I don't read Perl, I can't tell what you're trying to do. Can you spell it out more explicitly? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] simulate union in subselect
Wei Weng writes: > I know this is not do-able, but is there any way to simulate the > following in Postgresql 7.1? > > select id from > (select id, recv_date as date from table1 > union > select id, send_date as date from table2) AS subtable > order by date; I haven't actually tried whether this does not work, but ISTM that you could simply do select id, recv_date as date from table1 union select id, send_date as date from table2 order by 2; and ignore the second column when processing the result. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] simulate union in subselect
"Wei Weng" <[EMAIL PROTECTED]> writes: > I know this is not do-able, but is there any way to simulate the > following in Postgresql 7.1? > select id from > (select id, recv_date as date from table1 > union > select id, send_date as date from table2) AS subtable > order by date; What gives you the idea that this doesn't work? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] No JOINs in UPDATE ... FROM?
Folks, In 7.1 RC2, I've been trying to use the following JOIN syntax: UPDATE assignments SET status = -1 FROM assignments JOIN orders ON assignments.order_usq = orders.usq WHERE orders.status = -1; However, I get an error of "Relation assignments referenced twice in query." Now, I can (and have) re-phrase the query so that PostgreSQL will accept it. However, I was under the impression that the above was standard SQL92. Am I mistaken? Or is this form something that just hasn't been implemented yet? -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] random rows
Hi! > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. use the LIMIT clause example SELECT * FROM test_table LIMIT 100; you can also use the OFFSET clause to skip to n row and the fetch the n desired rows example SELECT * FROM test_table LIMIT 100 OFFSET 100; this will skip to row number 100 and the fetch the next 100 rows []´s - Joao Pedro M. F. Monoo Infortrade Information Systems #183816 Linux Registered User Slackware 7.1 running 2.4.2 Linux Kernel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] random rows
Jie, > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. You'd have to do it inside a function or external program, and copy the rows to a temporary table (which is what you'd return to the user). Thus, language-agnostic rules: CREATE FUNCTION return_random(X) LOOP X Times totalrecs = COUNT(*) FROM maintable WHERE NOT EXISTS temptable offset_count = RANDOM*totalrecs INSERT INTO temptable ( a, b, c, d ) SELECT a, b, c, d FROM maintable LIMIT 1 OFFSET offset_count; END LOOP END; than: SELECT temptable -Josh -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] use of arrow keys to traverse history
On Jue 26 Abr 2001 01:16, Grant wrote: > > That's because your version (talking about binaries) are not compiled > > with readline support. :-) > > Which binaries are not compiled with readline support? > > Why did this change from 7.0.3 -> 7.1? I don't have the slightest idea. Are you on rpms? If so, download the src.rpm and check the SPEC file for the config options, and then compile with rpm --rebuild Saludos... :-) -- El mejor sistema operativo es aquel que te da de comer. Cuida tu dieta. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] rules
Is it posible to make a rule execute more then one query? Something like: CREATE RULE rule_name AS ON INSERT TO table1 DO INSTEAD INSERT INTO table2 VALUES (new.value1,new.value2) INSERT INTO table3 VALUES (x,y) If not, is there a way to do this? Triggers maybe? Saludos... :-) -- El mejor sistema operativo es aquel que te da de comer. Cuida tu dieta. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: rules
On Thu, 26 Apr 2001, [iso-8859-1] Martín Marqués wrote: > Is it posible to make a rule execute more then one query? > > Something like: > > CREATE RULE rule_name AS ON INSERT TO table1 > DO INSTEAD > INSERT INTO table2 VALUES > (new.value1,new.value2) > INSERT INTO table3 VALUES > (x,y) test=# \h create rule Command: CREATE RULE Description: Defines a new rule Syntax: CREATE RULE name AS ON event TO object [ WHERE condition ] DO [ INSTEAD ] action where action can be: NOTHING | query | ( query ; query ... ) | [ query ; query ... ] ie CREATE RULE snog AS ON UPDATE TO foo DO INSTEAD ( ...; ... ); -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] rules
Martín Marqués wrote: > Is it posible to make a rule execute more then one query? > > Something like: > > CREATE RULE rule_name AS ON INSERT TO table1 > DO INSTEAD > INSERT INTO table2 VALUES > (new.value1,new.value2) > INSERT INTO table3 VALUES > (x,y) Yes: CREATE RULE rule_name AS ON INSERT TO table1 DO INSTEAD ( INSERT INTO table2 VALUES (new.value1,new.value2); INSERT INTO table3 VALUES (x,y); ); You just omitted the parens and semicoli :-) > > If not, is there a way to do this? Triggers maybe? Triggers too (even if yes above and effectively you haven't asked for): CREATE FUNCTION whatever () RETURNS opaque AS ' BEGIN INSERT INTO table2 VALUES (new.value1,new.value2); INSERT INTO table3 VALUES (...); RETURN NULL; -- returning NULL from a BEFORE trigger -- suppresses the triggering INSERT to -- happen. END;' LANGUAGE 'plpgsql'; CREATE TRIGGER table1_ins BEFORE INSERT ON table1 FOR EACH ROW EXECUTE whatever(); Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] No JOINs in UPDATE ... FROM?
Josh Berkus <[EMAIL PROTECTED]> writes: > UPDATE assignments SET status = -1 > FROM assignments JOIN orders ON assignments.order_usq = orders.usq > WHERE orders.status = -1; > However, I get an error of "Relation assignments referenced twice in > query." > Now, I can (and have) re-phrase the query so that PostgreSQL will > accept it. However, I was under the impression that the above was > standard SQL92. Am I mistaken? You are mistaken. SQL92 and SQL99 don't allow a FROM clause in UPDATE at all: they say it's just ::= UPDATE SET [ WHERE ] Postgres allows the clause, but treats it as supplying *additional* table references besides the target table reference. Thus the error. In other words: you can JOIN, but not against the target table. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Re: random rows
Jie Liang <[EMAIL PROTECTED]> wrote: >e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of >1000. Have you tried SELECT * FROM selectee ORDER BY random() LIMIT 100; ?o HTH, Ray -- JHM, jij bent echt nerd :)) maar wel een goeie :) Soort van programmerende furby Gezien op #cistron ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: random rows
On Thu, 26 Apr 2001, Jie Liang wrote: > > How I can return random N rows from my select stmt? > like: > e.g. what my selectee is a set of 1000 rows, I want randomly > pickup 100 of 1000. Interesting problem. You might get much better responses than this, but, two ideas that might be workable: * use a WHERE clause that checks random() > .88 . This should give you, on average, about 120 rows out of 1000, and you can add LIMIT 100 to ensure that you get only 100. But you're still biased toward the start of the list. (Or, remove the LIMIT 100, use > .9, but there's no guarantee you'll get 100-- you'll get more or less than that. * have a plpgsql routine that gets 100 random records, and copy these into a temporary table (since plpgsql can't return a recordset.) Query against this table. Or, when all else fails: * do it in your front end (Python/Perl/PHP/Pwhatever). If you get better ideas, and they aren't cc'd to the list, please do so. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] random rows
How I can return random N rows from my select stmt? like: e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of 1000. thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] simulate union in subselect
I know this is not do-able, but is there any way to simulate the following in Postgresql 7.1? select id from (select id, recv_date as date from table1 union select id, send_date as date from table2) AS subtable order by date; Thanks a lot Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Regexps -- too complex?
> SELECT id, title,publishdate,categoryid FROM articles WHERE > upper(title) ~ '(BLACK|SERIOUS|SAM)[[:>:]]'::text ; > > I think the proiblem is in trnsalte, not in regexp > > If you have installed apprporiate character encoding in Postgres, > 'upper' will work! > > Vladimir Thanks for the advice, unfortunately, it does not seem to work that way. CREATE TABLE "test" ( "title" text ); COPY "test" FROM stdin; Serious Sam ceïâ pie pircçjiem Black & White gaidîðanas svçtki Lorgaine: The Black Standard - íeltu varoòeposs Lorgaine: The Black Standard beta versija Black&White tomçr neesot spiegu programma Black & White FAQ Black & White "ïaunais" FAQ Black & White - pârdotâkâ spçle ASV \. SELECT title FROM test WHERE title ~ '(BLACK|WHITE|SAM)'; yields 8 rows. SELECT title FROM test WHERE title ~ '(BLACK|WHITE|blahblah|SAM)'; yields 0 rows! SELECT title FROM test WHERE title ~ '(BLACK|WHITE|SAM) *'; also yields 0 rows! I dont think this is right no matter what the characters I am using there. At least it shouldn't, should it? Emils ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] realising a counter in an pgpsql-trigger
Hi folks, I want to do the following: I have a column in every table, named c_update, it's purpose is to hold how many times the dataset was updated. This should be done automatically by the trigger - which already set sthe update-user, -time and date. How can I do this ??? chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] realising a counter in an pgpsql-trigger
Hate to be entirely off topic, but can anyone help me with changing to digest version of the listserv subscription? I've been all over the site, and can't find the instructions. >>> chris G*nther <[EMAIL PROTECTED]> 04/26/01 09:17AM >>> Hi folks, I want to do the following: I have a column in every table, named c_update, it's purpose is to hold how many times the dataset was updated. This should be done automatically by the trigger - which already set sthe update-user, -time and date. How can I do this ??? chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
