[SQL] Re: Order by in stored functions
On Mon, 4 Sep 2000, Tom Lane wrote: This is a bug that has already been fixed in current sources: the check that makes sure your select produces only one varchar column is mistakenly counting the hidden IdSort column that's needed to sort by. Is there any patch against 7.0.2 sources which might help me (or the Debian package maintainer out? I don't know of any good workaround in 7.0, short of patching the erroneous code. Have you thought about using a view, rather than a function returning set? I could try that. Is there any general advise for more or less beginners like me regarding when to use views and when to use functions? Kind regards Andreas.
Re: [SQL] Query on array-elements inside object
Hi, there I believe that if you create a function with PL/pgSQL that take a text array as parameter: create function cancer(_text) returns bool as ' declare . begin use a while loop here to scan the whole array if string match 'cancer' immediately return true otherwise end the loop return false end; ' language 'plpgsql'; Umashankar Kotturu wrote: Hi, I am new to Postgres as well as object database. Wondering if one of you can tell me on how to write a sql query to select an object that has a particular element in an array. Example If I create an object-table with 2 elements. a) PatientID - numeric b) DiseaseArray - array of text - This DiseaseArray has elements like "cancer", "tb" etc... Now how will I write a sql-query to select all patients that have "cancer" Any pointers will be helpful. regards, Uma. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] Re: [HACKERS] 7.0.2: Arrays
Ok, so I can't read. Thanks! LER * Kristofer Munn [EMAIL PROTECTED] [000905 15:27]: On Tue, 5 Sep 2000, Larry Rosenman wrote: Greetings, I was trying to use arrays today, and can't seem to get it right. What am I doing wrong? ler=# create table ia_standby (hsrp_group int2, ler(# router_interfaces[] varchar(64), ler(# routers[] varchar(64)); What you want to do is... create table ia_standby ( hsrp_group int2, router_interfaces varchar(64)[], routers varchar(64)[] ); - K Kristofer Munn * KMI * 732-254-9305 * AIM KrMunn * http://www.munn.com/ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [SQL] 7.0.2: Arrays
Hi, there Your syntax is not correct, pls check the Pg documentatation, the correction as following. Larry Rosenman wrote: Greetings, I was trying to use arrays today, and can't seem to get it right. What am I doing wrong? ler=# create table ia_standby (hsrp_group int2, ler(# router_interfaces[] varchar(64), ==router_interfaces varchar(64)[], ler(# routers[] varchar(64)); ERROR: parser: parse error at or near "[" ler=# create table ia_standby (hsrp_group int2, ler(# router_interfaces[] text, ==router_interfaces text[], ler(# routers[] text); ERROR: parser: parse error at or near "[" ler=# -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] HELP pg_proc is corrupted!
OK, I before any starts on my about backups...I have one a week old, but I want to fix this the right way! (and I want this past week's worth of data. What happened: o downloaded ip and macaddr type programs written by Paul Vixie et al., scrapped the ip stuff, and compiled the macaddr stuff. Ran the included sql script to load the "mac.so" file, create the custom macaddr type in and out functions, etc. At this point everything is good. o downloaded latest copy of IEEE MAC Address to Vendor list. Updated the mac.h file and recompiled the module. o Dropped all the functions and the types. o Re-ran the SQL script to create the handler, type, functions, etc after putting the new mac.so module in the proper path. o All hell breaks loose: o use functions are missing from pgaccess o pg_dump dies with invalid OID number(s) o multiple instances of macaddr definitions in pg_proc... o Tried to fix this with a reindex, but I was told that I had to drop the DB into single user mode, I tried this but the directions don't jive with pg_ctl nor with postmaster. I can't figure how to get to single mode Questions: 1. How do I to get the DB into single user mode? 2. How can I fix this problem? TIA. tim [EMAIL PROTECTED]
[SQL] array column -- do you really want this?
Hello Umashankar, *warning* this doesn't answer the question you asked *warning* you write that you are new to object databases. The problem that you describe sounds like it might much more completely be solved by not using the object features and avoiding an array alltogether. If you simply wish to solve the problem, then I can recommend some reading on normal forms, which goes into all the problems that can occur in databases without consideration for storage anomalities. You can find a good article here: http://home.earthlink.net/~billkent/Doc/simple5.htm Your database would then be in "first normal form" and not contain lists inside a field any more. You simply create two columns, the first listing the patient ID and the second listing the illness. Patient IDs are allowed to occur repeatedly. You could retrieve all illnesses for a particular patient by the use of: select illness from patient_illness where patient_id=1434; or you may retrieve all patients' illnesses by querying: select * from patient_illness order by patient_id,illness; I hope that helps, Oliver
Re: [SQL] Optimizing huge inserts/copy's
Jie Liang [EMAIL PROTECTED] writes: Hi, there, I tried different ways, include vaccum table , ensure index works, it still is as slow as ~100rows per minute. PGFSYNC=no in postmaster.init? Well, this might be Debian Linux specific, pardon me if it is. I have just begun playing with Postgres. Still learning, myself. IOW, disable fsync after every statement and your OS will do much better work clustering writes. That means more inserts/sec for you. In one of my tests I was able to insert at ~1000/sec rate. Then I made an experiment, enabled pgfsync _and_ indexes. The inserting speed dropped to 10/sec. Very interesting. Regards, -- Zlatko
[SQL] Re: [HACKERS] 7.0.2: Arrays
On Tue, 5 Sep 2000, Larry Rosenman wrote: Greetings, I was trying to use arrays today, and can't seem to get it right. What am I doing wrong? ler=# create table ia_standby (hsrp_group int2, ler(# router_interfaces[] varchar(64), ler(# routers[] varchar(64)); What you want to do is... create table ia_standby ( hsrp_group int2, router_interfaces varchar(64)[], routers varchar(64)[] ); - K Kristofer Munn * KMI * 732-254-9305 * AIM KrMunn * http://www.munn.com/
[SQL] Outer join statement ?
The statement "Outer Join" is not available in Postgres ? Is there an extension provided by a third company that implement this functionnality ? If not do you know when this functionnality will be available in Postgres ? Thank Fred -- TechBourse : le premier site en FRANCAIS dédié le NASDAQ http://www.techbourse.com
[SQL] Protection of tables by group and not by users
Hello, Is it possible to protect a table of Postgresql by a group of persons instead of giving a list of persons ? Thanks for your answers Regards nicolas michaud
