[GENERAL] trigger insert duplicate rows question
I have designed a trigger that will insert a row in a table if a check to see if the row is already there fails. Everything works as expected when running slow or with few threads. but when I kick up the threads that handle the initial insert that 'triggers' the trigger I run into some trouble. It seems that in some cases after the trigger function has done a selct to see it the data already exist then moves on to the insert the insert fails because another thread has already finished inserting the same row. I get an error about duplicate rows etc. This in itself is really not a problem. What I would like to do is suppress the error message somehow. I am using jdbc on the client side and can't figure out how to suppress the error from going to stdout. Can I capture it in the procedure code? Any other suggestions? Is there a way to get a return from the insert statement in the prodecure? thanks in advance. t.r. missner ---(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
[GENERAL] WHERE CLAUSE
Hi,
Well i have seen somewhere
WHERE somefiled @ '{123,324}'
what does this mean
Sameer
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] test
Sorry for test post. lt ss 9/26/2001 »úÃÜ ÉîÛÚÊзçÁÖ»ðɽµçÄÔ¼¼ÊõÓÐÏÞ¹«Ë¾ µÚ1Ò³/¹²1Ò³
[GENERAL] Odd query
Let's say I have a table :
create table people (
person_id integer not null primary key,
firstnames varchar(50),
lastname varchar(50)
);
and another
create table partners (
first_person integer not null references people(person_id),
second_person integer not null reference people(person_id)
);
Let's do some test data:
select setval('people_seq',0);
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'Prince Of', 'Darkness');
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'William Henry', 'Gates');
insert into people (person_id, firstnames, lastname) values
(nextval('people_seq'), 'Steve', 'Case');
insert into partners (first_person, second_person) values (1,2);
insert into partners (first_person, second_person) values (1,3);
Now is it possible for me to do a select statement without any
subqueries to get the firstnames and lastname of each member of a
partnership. e.g.
first_firstnames | first_lastname | second_firstnames | second_lastname
-++---+
Prince Of| Darkness | William Henry | Gates
Prince Of| Darkness | Steve | Case
Thanks for any help anybody can give...
-jag
--
Joshua Ginsberg [EMAIL PROTECTED]
Director of Technology [EMAIL PROTECTED]
Student Association AIM: L0stInTheDesert
Rice University, Houston, TXCellphone: 713.478.1769
"Programming today is a race between software engineers
striving to build bigger and better idiot-proof programs
and the Universe trying to produce bigger and better
idiots. So far, the Universe is winning." -Rich Cook
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] postgresql.conf
On Tuesday 25 September 2001 11:34 am, Mihai Gheorghiu wrote: > I installed PG from RPMs. postgresql.conf comes with all options commented > out. > What are the defaults? PG works anyway (Well... I know... -i etc.) > Thank you all. All options commented out is the installation default of a from-source install as well as the RPM install. The default values for the various paramters are commented inside the file, IIRC. Use tcpip_socket=true instead of -i I made the conscious decision to ship the default postgresql.conf -- what optimizations should I make? I can't make generalized optimizations -- so I ship the default file. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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: [GENERAL] trigger compile problem
I wasn't sure about the quotes and have taken them off ( didn't have them in the beginning ) but the problem still exists. I was just looking at other trigger code and was wondering if maybe I need a ; after the last END? -Original Message- From: Doug McNaught [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 25, 2001 5:36 PM To: Missner, T. R. Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] trigger compile problem [EMAIL PROTECTED] writes: > Can anyone out there help me. > I have created a trigger and can't get it to run. > When it is invoked I get a compile error no matter what. > .I have changed the code in the trigger many times and it really doesn't > matter what the code actually does it still won't compile. > I have stripped it down so all it does is return NEW and still the problem. It's not clear where the problem is from your post, though you seem to have made some mistakes, which I will point out below. Want to post your CREATE TRIGGER statement as well? > here is the error > > ERROR: parse error at or near "" > > here is the code > > CREATE FUNCTION "AANEW" () RETURNS opaque AS ' > DECLARE myrec sipmsg_lu%ROWTYPE; > BEGIN > SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid''; Why are you quoting NEW.callid? As written above you are comparing sipmsg_lu.callid to the literal string 'NEW.callid' which is almost certainly not what you want. Take the quotes off. > IF NOT FOUND THEN > INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid''); Same here. Otherwise nothing looks obviously wrong. Are you writing the trigger code on a Windows machine? Is it possible that there are ^M characters in the text? -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm.-Dylan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] trigger compile problem
[EMAIL PROTECTED] writes: > Can anyone out there help me. > I have created a trigger and can't get it to run. > When it is invoked I get a compile error no matter what. > .I have changed the code in the trigger many times and it really doesn't > matter what the code actually does it still won't compile. > I have stripped it down so all it does is return NEW and still the problem. It's not clear where the problem is from your post, though you seem to have made some mistakes, which I will point out below. Want to post your CREATE TRIGGER statement as well? > here is the error > > ERROR: parse error at or near "" > > here is the code > > CREATE FUNCTION "AANEW" () RETURNS opaque AS ' > DECLARE myrec sipmsg_lu%ROWTYPE; > BEGIN > SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid''; Why are you quoting NEW.callid? As written above you are comparing sipmsg_lu.callid to the literal string 'NEW.callid' which is almost certainly not what you want. Take the quotes off. > IF NOT FOUND THEN > INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid''); Same here. Otherwise nothing looks obviously wrong. Are you writing the trigger code on a Windows machine? Is it possible that there are ^M characters in the text? -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm.-Dylan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] trigger compile problem
Can anyone out there help me. I have created a trigger and can't get it to run. When it is invoked I get a compile error no matter what. .I have changed the code in the trigger many times and it really doesn't matter what the code actually does it still won't compile. I have stripped it down so all it does is return NEW and still the problem. here is the error ERROR: parse error at or near "" here is the code CREATE FUNCTION "AANEW" () RETURNS opaque AS ' DECLARE myrec sipmsg_lu%ROWTYPE; BEGIN SELECT INTO myrec * FROM sipmsg_lu WHERE callid=''NEW.callid''; IF NOT FOUND THEN INSERT INTO sipmsg_lu VALUES (NEW.time , ''NEW.callid''); END IF; RETURN NEW; END' LANGUAGE 'plpgsql'; any help would be great. red hat 2.2 kernal pgsql 7.1.3 t.r. missner ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Functions
Mihai Gheorghiu wrote: > I created 2 functions: f1 and f2. f1 calls f2. I then change f2. > What is the minimum I need to do for f1 to see the modified f2? > Is my understanding correct that I need to drop and recreate f1? > Thank you in advance. For functions called by functions, it should be sufficient to reconnect to the database. AFAIK neither 'sql', nor any existing PL's can cache function OIDs across DB connection time. 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Functions
I created 2 functions: f1 and f2. f1 calls f2. I then change f2. What is the minimum I need to do for f1 to see the modified f2? Is my understanding correct that I need to drop and recreate f1? Thank you in advance. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Practical Cursors
Micah Yoder wrote: > (sorry to reply to a week-old message. need to keep up with this list more!) > > On Monday 17 September 2001 17:04, you wrote: > > > There is an obvious benefit to the use of cursors within a persistent > > environment. In other words, if my connection to the database is live, I > > can increase my query and display efficiency through the use of a cursor. > > > > However, this seems to be useless within a web based environment. If we > > have a live connection through a C++ application, we can perform a > > transaction and interact within the results. > > Yep. That seems to be a disadvantage with ALL database systems & HTTP based > apps. > > I once wrote a MySQL app (I know, but that's what the company used) to do a > fairly complicated search on a huge database full of domain names. The query > was time consuming (10-30 seconds) so it obviously could not be performed for > every prev/next page request. > > My first approach was to have the PHP script write the entire data resultset > to a fixed-length file, which could be easily accessed for each request to > the point where the user was in the file. Only problem there was when the > result set was large, initial query time was significantly longer. And that > happened a lot. > > I then wrote a daemon in C to do the work and store the results in RAM. The > PHP script connected to the daemon via a socket, and passed a request ID and > the numbers of the records it wanted. Sure, it was convoluted, but I > actually got the speed up to where I was fairly happy with it. > > If there's a better solution than that, I'm not aware of it. > > But like someone else mentioned, it's not quite "practical" database usage. Since search engines and data warehousing tend to have huge databases with sometimes complicated, long running queries that produce empty to huge result sets, it's a quite common problem. Thus, I would consider any solution that leads to success at first "practical". PHP together with cursors might be an alternate solution. You open a cursor for the entire result set. You have a function that fetches the next n rows from the cursor and generates the resulting html output in a file. It returns true if more rows have been found. You call it once and if it returns false display "No match found" or so. If it returns true, you call it again to create a cache file for the second result page, and know if there will be one (telling you if to provide a NEXT button). You register a shutdown function that will call the cache file generator another m times. Now you display the first cache file, leave the DB connection with the open transaction and cursor where they are and exit. The user will already see the first result page while your server is still working. After calling the cache file generator function m times, the shutdown function closes the cursor, terminates the transaction and closes the DB connection. I think 95% of users will not hit NEXT more than 10 times before refining their search, so that should be enough. If one really does, well, than you'd have to run the entire query again and this time create either more cache files or all of them. Now you need some sort of vacuum cleaner for the cache files and are done. The drawback for this solution is, that you don't know how many pages there will be in total when you display the first one. But the benefits are that it fit's into the connectionless HTTP nature, has a small resource footprint, provides first results early and does not require open transactions over user interaction. 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] not on .hackers
"Colin 't Hart" <[EMAIL PROTECTED]> wrote in message news:<9oo6en$qr8$[EMAIL PROTECTED]>... > August Zajonc: > > > I tend to follow the mailing list through news.postgresql.org, and it > seems > > like all the -hackers messages are ending up in the .general group rather > > than .hackers. > > I also follow the mailing list(s) through news.postgresql.org and now that > you mention it ... > > It hasn't always been like this. I'm getting the same thing. Almost nothing is going to the Hackers list on Google groups (old Dejanews). They all seem to be sent to other lists with the [HACKERS] id in the subject. -Tony ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Function exists
On Tue, Sep 25, 2001 at 02:20:23PM -0400, Mihai Gheorghiu wrote: > I want to write a function that finds out whether a function already exists. > Is that possible? How? select * from pg_proc where proname = ??; you can run psql with switch -E, then it shows intenal queries it performs. Eg., for \df - 'show functions' it does: $ psql -E marko=# \df * QUERY * SELECT format_type(p.prorettype, NULL) as "Result", p.proname as "Function", oidvectortypes(p.proargtypes) as "Arguments" FROM pg_proc p WHERE p.prorettype <> 0 and (pronargs = 0 or oidvectortypes(p.proargtypes) <> '') ORDER BY "Function", "Result", "Arguments" * from there you can hack further. -- marko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Function exists
I want to write a function that finds out whether a function already exists. Is that possible? How? Thank you all. ---(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: [GENERAL] Encoding passwords
> Is there a function out there for pg which allows you to generate > a random > number given a seed value? I'm trying to create a users table > which would There is the following: "select setseed();" This sets the seed for the random() function. However, the approach we use is more like the suggestion from Bruno Wolf that you received earlier- In our case we use JDBC to pass data between our application & the database, so we use the java crypt package to encrypt everything we get before it gets stored or compared to a stored value & then just compare the hash. I think his suggestion is the best way *if* your development environment supports something similar. -Nick - Nick Fankhauser Business: [EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ Personal: [EMAIL PROTECTED] http://www.fankhausers.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] upper case constraint?
One turnarround solution I use for that is to make the convertion to uppercase in the script prior to the insertion. If that is your case, u can do that too. Nevertheless, it seem easy to make a function toupper(), if it does not exists yet On Tue, Sep 25, 2001 at 12:43:58PM -0400, Gowey, Geoffrey wrote: > Just wondering if anyone knows off hand how to make it so all inserts on a > column will wind up being tranparently stored as uppercase (ex: ee1234567 = > EE1234567). > > Geoff > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Pedro Miguel G. Alves THINK - Tecnologias de Informação Av. Defensores de Chaves nº 15 4ºD, 1000-109 Lisboa Portugal Tel: +351 21 3590285 Fax: +351 21 3582729 HomePage: www.think.co.pt ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] upper case constraint?
On Tue, 25 Sep 2001, Gowey, Geoffrey wrote: > Just wondering if anyone knows off hand how to make it so all inserts on a > column will wind up being tranparently stored as uppercase (ex: ee1234567 = > EE1234567). Probably a trigger, before update/insert on table: create function gouppertable_col() returns opaque as ' begin NEW.col := upper(NEW.col); return NEW; end; ' language 'plpgsql'; ---(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: [GENERAL] upper case constraint?
"Gowey, Geoffrey" <[EMAIL PROTECTED]> writes: > Just wondering if anyone knows off hand how to make it so all inserts on a > column will wind up being tranparently stored as uppercase (ex: ee1234567 = > EE1234567). Sounds like a good application for a trigger. -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm.-Dylan ---(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: [GENERAL] Encoding passwords
On Tue, Sep 25, 2001 at 08:42:04AM -0400, Mike Arace wrote: > > Is there a function out there for pg which allows you to generate a random > number given a seed value? I'm trying to create a users table which would > require the storage of a password in a database field, and I'm hesitant to > put it in there in plain text, despite the fact I plan to put very tight > restrictions on that particular table. Ideally, I would encode each letter > one by one, using the random number generator with the previous letter as a > seed for the next. I was told that certain unixes use a similar way to > store their passwords, and it seemed to make sense for this application. I > noticed that there is a rand() function, but I'm a little slow today and > couldn't think a way to use that in this scenario. Any suggestions would be > > greatly appreciated. Look into contrib/pgcrypto in CVS, or http://www.l-t.ee/marko/pgsql/pgcrypto-0.3.tar.gz in meantime. Gives you crypt() function not unlike in UNIXes. Also gen_salt() for generating salts for it. -- marko ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] upper case constraint?
Just wondering if anyone knows off hand how to make it so all inserts on a column will wind up being tranparently stored as uppercase (ex: ee1234567 = EE1234567). Geoff ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] postgresql.conf
I installed PG from RPMs. postgresql.conf comes with all options commented out. What are the defaults? PG works anyway (Well... I know... -i etc.) Thank you all. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] virtual filesystem atop a PostgreSQL database
On Tue, Sep 25, 2001 at 02:45:37PM +0200, Jan Pruner wrote: > Hmm, filesystem IS database. > If you need SQL-like functionality to ask for something in your fs why do > you want to mount db like fs? > You can build a sql-like shell !?! EnhancedBASH? I couldn't possibly explain it any better than Hans Reiser (BTW, he's the author of ReiserFS, just in case that is not known) in http://www.namesys.com/whitepaper.html In particular, look at the section entitled 'The Little Inconveniences Dominate What We Do'. To be sure, what I suggested is emphatically NOT what Reiser is aiming for; it just adresses some of the issues he raises. Regards, Frank ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] virtual filesystem atop a PostgreSQL database
> I am wondering whether anyone has already tried it, or if not, looking > for starting points as to how to go about doing it: I recall seeing an article in a magazine a couple of years ago by someone using PostgreSQL to implement a versioning file system on Linux. I *think* it was in Linux Journal. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Encoding passwords
On Tue, Sep 25, 2001 at 08:42:04AM -0400, Mike Arace <[EMAIL PROTECTED]> wrote: > > Is there a function out there for pg which allows you to generate a random > number given a seed value? I'm trying to create a users table which would > require the storage of a password in a database field, and I'm hesitant to > put it in there in plain text, despite the fact I plan to put very tight > restrictions on that particular table. Ideally, I would encode each letter > one by one, using the random number generator with the previous letter as a > seed for the next. I was told that certain unixes use a similar way to > store their passwords, and it seemed to make sense for this application. I > noticed that there is a rand() function, but I'm a little slow today and > couldn't think a way to use that in this scenario. Any suggestions would be > greatly appreciated. The more normal way to do this is to store a cryptographic hash of the password in the database and have the application calculate the hash and compare that to the hash in the database. This approach won't work if the database is used to store passwords for use by applications in connecting to other services. Typical cryptographic hash functions are SHA-1 and MD5 and you shouldn't have much trouble finding libraries that provide these functions. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Fwd: Re: [GENERAL] virtual filesystem atop a PostgreSQL database
Hmm, filesystem IS database. If you need SQL-like functionality to ask for something in your fs why do you want to mount db like fs? You can build a sql-like shell !?! EnhancedBASH? JP On Tue 25. September 2001 14:22, you wrote: > I am wondering whether anyone has already tried it, or if not, looking > for starting points as to how to go about doing it: > > The idea would be to have some kind of tree implementation (e.g. > pointers or nested sets) for an SQL database and then to write a Linux > driver that would make it possible to create a device file so that you > could do something like > > mount -t (specify filesystem: e.g. ext2, vfat) /dev/posttree /mountpoint > > so that the SQL tree would look like a normal filesystem. Read-only > would be cool to start with, although it'd become really useful if you > had an rw implementation with permissions etc.. (Richard Jones has done > something which is kind of similar for his ftp server: You can use a > PostgreSQL database as a backend for it, rather than a filesystem: > http://www.cpan.org/modules/by-authors/id/R/RW/RWMJ/) > > How difficult would that be? Where to start? Where to find code snippets > to work with? Which filesystem would be the most suited for an emulation > - ext2,vfat . . . ? Whould that need to be done in C or could you write > a wrapper/driver in something like e.g. Perl? > > The inspiration for this idea was Hans Reiser's manifesto 'The Naming > System Venture' where he argues that the future belongs to filesystems > with database-like extensions, rather than databases. He may be > right or not; but what kept me thinking above all was that I > do encounter the problem that he describes: Whenever I want to put > something into a database, or retrieve something from it, I am always > depending on more or less specialized interfaces (I use PHP) which may > not be available to some user at some point, and things then tend to > become cumbersome. Hans Reiser's argument is actually somewhat more > sophistaced and lenghty, which is why I am not trying to reproduce it > here. A tool like the one I tried describe would make it possible to > combine the flexibility of a filesystem with the more specialized > functionality of an SQL database. > > Regards, Frank > > ---(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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Encoding passwords
Hey everyone, Is there a function out there for pg which allows you to generate a random number given a seed value? I'm trying to create a users table which would require the storage of a password in a database field, and I'm hesitant to put it in there in plain text, despite the fact I plan to put very tight restrictions on that particular table. Ideally, I would encode each letter one by one, using the random number generator with the previous letter as a seed for the next. I was told that certain unixes use a similar way to store their passwords, and it seemed to make sense for this application. I noticed that there is a rand() function, but I'm a little slow today and couldn't think a way to use that in this scenario. Any suggestions would be greatly appreciated. Thanks Mike _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] virtual filesystem atop a PostgreSQL database
I am wondering whether anyone has already tried it, or if not, looking for starting points as to how to go about doing it: The idea would be to have some kind of tree implementation (e.g. pointers or nested sets) for an SQL database and then to write a Linux driver that would make it possible to create a device file so that you could do something like mount -t (specify filesystem: e.g. ext2, vfat) /dev/posttree /mountpoint so that the SQL tree would look like a normal filesystem. Read-only would be cool to start with, although it'd become really useful if you had an rw implementation with permissions etc.. (Richard Jones has done something which is kind of similar for his ftp server: You can use a PostgreSQL database as a backend for it, rather than a filesystem: http://www.cpan.org/modules/by-authors/id/R/RW/RWMJ/) How difficult would that be? Where to start? Where to find code snippets to work with? Which filesystem would be the most suited for an emulation - ext2,vfat . . . ? Whould that need to be done in C or could you write a wrapper/driver in something like e.g. Perl? The inspiration for this idea was Hans Reiser's manifesto 'The Naming System Venture' where he argues that the future belongs to filesystems with database-like extensions, rather than databases. He may be right or not; but what kept me thinking above all was that I do encounter the problem that he describes: Whenever I want to put something into a database, or retrieve something from it, I am always depending on more or less specialized interfaces (I use PHP) which may not be available to some user at some point, and things then tend to become cumbersome. Hans Reiser's argument is actually somewhat more sophistaced and lenghty, which is why I am not trying to reproduce it here. A tool like the one I tried describe would make it possible to combine the flexibility of a filesystem with the more specialized functionality of an SQL database. Regards, Frank ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] TEXT in selection?
Hi,
does anybody know a way, how to implement the content of a field of type
TEXT
into a query?
if i am trying:
select * from testtable WHERE testTEXT LIKE '%testString%'
i am getting always an empty selection, although there is a record existing.
The same with
select * from testtable WHERE position ('testString' in testTEXT) >
0;
Additional, is there a way to use the UPPER()-function to the content of the
TEXT-field?
Thanks in advance,
Michael
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Second byte of multibyte characters causing trouble
> > Use "kon" command. > This was a wonderful tip - thank you, Ishii-san! I didn't know about the > command, and it seems to be trying to do what it is designed to do. It > doesn't display Shift-JIS correctly, but it does work for EUC. Since I seem > to be moving in the direction of converting everything to EUC, that should > be okay. But in vi, how do I input Japanese? Is there a key combination > that does what IME does in Windoze? I've heard that there is a vi crone called "nvi-canna" having a IME called "canna". In nvi-canna, "set canna" + CTRL-O should initiate the Japanese input mode. > I also noticed that vi is still not aware of the multi-byte characters - for > example, when moving around in the text, I have to type h or l twice to get > to the next character, and if I want to copy or delete characters I have to > pretend that there are twice as many. Typing "x" just once (or an odd > number of times) is really entertaining - all the characters in a whole word > or sentence change to obscure kanji as kon tries to process the second byte > of one character and the first byte of the next as a character. Is there a > way to make vi aware of multibyte characters? (This is not an absolute > necessity, but would help.) Hmm. Maybe locale problem? In my case (I'm using Vine Linux), the correct locale is "ja_JP.eucJP", but may be different on RH6.2J. > > Again why not emacs? > I had never used it - in fact, it wasn't even installed on my system. After > you seemed to be recommending it, I installed the "no X" version (I don't > have any graphical interfaces on these machines) and invoked it once to see > what it is like, but it looks like it would be miserable to learn to use > without a mouse, if it would work at all for some features (I had to dig > real deep in the docs to figure out key commands - they constantly refer to > the mouse). It would be no problem to add a mouse to the server that > resides at my desk (well, maybe a bit of a desk space shortage...), but much > of my work is done through ssh to two other servers, and I doubt a mouse > would work in that environment - am I wrong? (Zero experience with mice in > Linux!) I've never use a mouse with Emacs even in the X environment. > I do read and write Japanese if I work hard enough at it (lots of copy/paste > to/from a software dictionary - I've lived in Japan 5 years), but reading > and contributing to a mailing list in Japanese could consume my whole work > day! :-) I understand. Same thing can be said for me with English:-) > > Are yo using PHP? Then I strongly recommend upgrade to PHP 4.0.6 or > > higher. It supports Japanese very well. It aumatically guess the input > > charset, does the neccessary conversion. > Input from where and conversion to what? Do you mean data typed into forms? Yes. It seems most browsers use same encoding as the one used for the the page (you could view the page's charset using "property" or whatever menu in the browser). PHP4.0.6 is clever enough to automaticall guess the chaset of data put in a form, and do a conversion between the encoding and EUC-JP (that is the recommended internal encoding of PHP). In PHP4.0.6's php.ini there are entries to control the behavior of the encoding handling: mbstring.internal_encoding = EUC-JP mbstring.http_input = auto mbstring.http_output = SJIS Theses are read: o the internal encoding in PHP is EUC-JP (this is recommended) o charsets of any input from forms etc. are automatically determined o charsets for all final pages produced by PHP are SJIS Interesting?:-) -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Second byte of multibyte characters causing trouble
> Use "kon" command. This was a wonderful tip - thank you, Ishii-san! I didn't know about the command, and it seems to be trying to do what it is designed to do. It doesn't display Shift-JIS correctly, but it does work for EUC. Since I seem to be moving in the direction of converting everything to EUC, that should be okay. But in vi, how do I input Japanese? Is there a key combination that does what IME does in Windoze? I also noticed that vi is still not aware of the multi-byte characters - for example, when moving around in the text, I have to type h or l twice to get to the next character, and if I want to copy or delete characters I have to pretend that there are twice as many. Typing "x" just once (or an odd number of times) is really entertaining - all the characters in a whole word or sentence change to obscure kanji as kon tries to process the second byte of one character and the first byte of the next as a character. Is there a way to make vi aware of multibyte characters? (This is not an absolute necessity, but would help.) > Again why not emacs? I had never used it - in fact, it wasn't even installed on my system. After you seemed to be recommending it, I installed the "no X" version (I don't have any graphical interfaces on these machines) and invoked it once to see what it is like, but it looks like it would be miserable to learn to use without a mouse, if it would work at all for some features (I had to dig real deep in the docs to figure out key commands - they constantly refer to the mouse). It would be no problem to add a mouse to the server that resides at my desk (well, maybe a bit of a desk space shortage...), but much of my work is done through ssh to two other servers, and I doubt a mouse would work in that environment - am I wrong? (Zero experience with mice in Linux!) > Assuming you could read/write Japanese, I recommend you subscribe > PHP-users list (http://ns1.php.gr.jp/mailman/listinfo/php-users). I do read and write Japanese if I work hard enough at it (lots of copy/paste to/from a software dictionary - I've lived in Japan 5 years), but reading and contributing to a mailing list in Japanese could consume my whole work day! :-) That's why I have been using English lists. But I know that most of the people on the English lists (maybe everybody except Ishii-san!) don't work with Japanese systems and can't answer questions about them, so when I have future questions of this type, I probably should try the php.gr.jp list. Thanks for the link. > Are yo using PHP? Then I strongly recommend upgrade to PHP 4.0.6 or > higher. It supports Japanese very well. It aumatically guess the input > charset, does the neccessary conversion. Input from where and conversion to what? Do you mean data typed into forms? (I had assumed that I control the charset used for form data by the "charset" variable in the html header, but I haven't tested that theory!) Or do you mean that if the text in echo statements is in a different charset than the header (how could it even know?), it will convert it when sending it out to the browser? (That would be hard to believe, but wonderful if it's true!) I'm still unsure of what to do. I was just about to take your advice and switch all my PHP and Perl files to EUC, when I remembered that I have to consider other people. After I get the PHP/Perl code working, the webmaster cleans up my grammar and/or changes the wording to the way he wants it, and he never uses Linux but only Windows-based editors, which as far as I know all expect Shift-JIS. Maybe I can train him to always open files with the EUC->Shift-JIS preprocessor and save them with the Shift-JIS->EUC postprocessor, but I suspect he won't be happy about it. But if I can get answers to the above questions, I may be closer to a decision on which approach is better, all things considered. Regards, Karen Karen Ellrick S & C Technology, Inc. 1-21-35 Kusatsu-shinmachi Hiroshima 733-0834 Japan (from U.S. 011-81, from Japan 0) 82-293-2838 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Practical Cursors
(sorry to reply to a week-old message. need to keep up with this list more!) On Monday 17 September 2001 17:04, you wrote: > There is an obvious benefit to the use of cursors within a persistent > environment. In other words, if my connection to the database is live, I > can increase my query and display efficiency through the use of a cursor. > > However, this seems to be useless within a web based environment. If we > have a live connection through a C++ application, we can perform a > transaction and interact within the results. Yep. That seems to be a disadvantage with ALL database systems & HTTP based apps. I once wrote a MySQL app (I know, but that's what the company used) to do a fairly complicated search on a huge database full of domain names. The query was time consuming (10-30 seconds) so it obviously could not be performed for every prev/next page request. My first approach was to have the PHP script write the entire data resultset to a fixed-length file, which could be easily accessed for each request to the point where the user was in the file. Only problem there was when the result set was large, initial query time was significantly longer. And that happened a lot. I then wrote a daemon in C to do the work and store the results in RAM. The PHP script connected to the daemon via a socket, and passed a request ID and the numbers of the records it wanted. Sure, it was convoluted, but I actually got the speed up to where I was fairly happy with it. If there's a better solution than that, I'm not aware of it. But like someone else mentioned, it's not quite "practical" database usage. -- Like to travel?http://TravTalk.org Micah Yoder Internet Development http://yoderdev.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
