[SQL] Query on array-elements inside object
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.
Re: [SQL] Re: Order by in stored functions
Hi, My experience is when you involoved that you have to use some CONTROL LANGUAGE such as LOOP, IF ... ELSE ... or value transfer (use variables), then using function, otherwise using view, temp table... In Postgres, function is another way to store procedure. Andreas Tille wrote: > 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. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
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] 7.0.2: Arrays
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)); ERROR: parser: parse error at or near "[" ler=# create table ia_standby (hsrp_group int2, ler(# 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
[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
[SQL] Auto increment
Hi
I'm a newbiw with pgsql:
1: Haven't been able to find the officiel manual to pgsql. What's the
complete URL?
2: How can I make an auto increment with PostGreSQL? is it "inherit"?
3: The "SHOW TABLE FROM pcfocus" statement, does it work in pgsql?
If yes, what kind of variable will be "produced" and how should I parse
it PHP?
Is this right:
$sql = "SHOW TABLES FROM mydb";
$test = pg_exec($connect, $sql);
while(list($table) = each($test)){
echo $test."\n";
?
Thanks alot!
--
Med venlig hilsen/ Wishes
Mads Jensen
Homepage: http://www.pcfocus.f2s.com
E-Mail: [EMAIL PROTECTED]
--
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
[SQL] Cascading Deletes
Hi, I have a tables having this structure: ID (int) | _ID (int) | Name (String) _ID is the parent of ID. I'm looking for the best method to perform a cascade delete. For example, I delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it would continue through the chain. For example: 0 0 Base 1 0 Sib1 2 0 Sib2 3 0 Sib3 4 1 Sib1_1 5 1 Sib1_2 Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 and Sib1_2. Can anyone help out here? Regards, Craig May Enth Dimension http://www.enthdimension.com.au
[SQL] 8K Limit, and Replication
Hello, I have heard of this infamous 8k limit. I have a couple of questions. 1. Does this mean that if I have a large object that I am inserting into a table, like an image it has to be 8k or less? 2. When will this be fixed? 3. Does anyone know the status of the replication capabilities in PGSQL? J On Wed, 6 Sep 2000, Craig May wrote: >Hi, > >I have a tables having this structure: > >ID (int) | _ID (int) | Name (String) > > >_ID is the parent of ID. > >I'm looking for the best method to perform a cascade delete. For example, I >delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it >would continue through the chain. > >For example: > >0 0 Base >1 0 Sib1 >2 0 Sib2 >3 0 Sib3 >4 1 Sib1_1 >5 1 Sib1_2 > > >Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 >and Sib1_2. >Can anyone help out here? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au > -- -- CommandPrompt - http://www.commandprompt.com OpenDocs, LLC. - http://www.opendocs.org LinuxPorts - http://www.linuxports.com LDP - http://www.linuxdoc.org -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
Re: [SQL] 8K Limit, and Replication
On Tue, 5 Sep 2000, Poet/Joshua Drake wrote: > Hello, > > I have heard of this infamous 8k limit. I have a couple of questions. > 1. Does this mean that if I have a large object that I am inserting into a > table, like an image it has to be 8k or less? In current version is possible range 8--32Kb for block size, default is 8Kb. You can change it in sourses in the file include/config.h, other solution is use the large object interface (LO). > 2. When will this be fixed? It's already fixed in the current devel tree (see CVS) and it will available in 7.1 (1 Oct?). > 3. Does anyone know the status of the replication capabilities in PGSQL? Good question, bad answer ... (IMHO) not exist some standard replication for PG. Karel
[SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
[EMAIL PROTECTED] wrote: > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > on a RH6.2 install. > > I have a few questions on it and the use of the -E flag. > > 1 - can 7.0.2 be optimized for i686 architecture or is > it only possible to compile for i386 architecture? Yes. Changed C compilation flags in template file for your operating system. > > Max Pyziur BRAMA - Gateway Ukraine > [EMAIL PROTECTED] http://www.brama.com/
[SQL] Re: [ADMIN] 7.0.2 questions on encoding and compilation
I have compiled for i686 by hacking up my RPM compile environment. On Wed, 30 Aug 2000 [EMAIL PROTECTED] wrote: > Currently, I'm using the the 7.0.2 rpms from the postgresql.org > on a RH6.2 install. > > I have a few questions on it and the use of the -E flag. > > 1 - can 7.0.2 be optimized for i686 architecture or is > it only possible to compile for i386 architecture? > > 2 - Can createdb -E someencoding be used "straight out of the box" > with Postgresql 7.0.2 or does support need to be compiled into > the function? > > 3 - What are the list of arguments which can be taken with the > command createdb -E ? > > > > Thanks! > > > > > Max Pyziur BRAMA - Gateway Ukraine > [EMAIL PROTECTED] http://www.brama.com/ >
[SQL] Re: [GENERAL] function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > how can I write function which takes text from one field, replaces > some characters and puts it in other field? I have array with old and > new values. Probably best to do this as an embedded perl sql function. Then it is about 3 lines. Ian -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5rbnTfn9ub9ZE1xoRAh7uAKCAxqAM9Wo09g6qntF6qJTtp5u+KACgsO9d VvH5BBpn4gIpdrHFAw8x6Jo= =zH55 -END PGP SIGNATURE-
