Re: [SQL] contrib/intarray doc/tutorial needed
O Oleg Bartunov έγραψε στις Jan 26, 2005 : > Achilleus, > > I'm looking for better documentation for contrib/intarray. > As an experienced user of this module, could you prepare > tutorial on using the module in real life applications ? Oleg, i wrote to you, but i suspect my machine is spam listed on your site. If this is the case, i'll try to contact you from yahoo. > Seems, many people just don't understand that intarray could > help them in many situations. > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What's the equivalent in PL/pgSQL
On Jan 27, 2005, at 11:10 AM, KÖPFERL Robert wrote: That's bad. Is there really no ohter way? So it takes TWO termoral tables. Or even more? I'm not sure what is bad. In what sense is it bad? How does more than one table come into things? And it can't be just sql because theres more around that statement. I'm not sure what you mean. Which statement? -Original Message- From: Sean Davis [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 27. Jänner 2005 14:57 To: KÖPFERL Robert Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] What's the equivalent in PL/pgSQL See this section of the manual: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html In particular, look at 35.7.1.2 and 35.7.4. I think you need to loop through the results of the query in order to return them. If you just want to return the result set as a whole to another function that needs to work with the data, you can use a cursor. Sean On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] hardware mod based on feedback from the list
On Thu, 27 Jan 2005 17:11:55 +, Richard Huxton wrote > > Joel Fradkin wrote: > > > > I was told the Xeon processors will do fine up to 64gig. I > > realize the 64bit chips may be faster, but it is also new > > and I feel safer with existing technologies and hardware > > vendors. > > > > My understanding is that CPU (4 Xeon 3gig processors) will > > not be a issue, but hopefully adding the additional drive > > systems will help the most for IO is what I am told is the > > big issue and hopefully utilizing the recommendation will > > help minimize the bottleneck. > > I believe there may well be an issue with multiple Xeon's - check > the mailing list archives for details. > > -- >Richard Huxton >Archonet Ltd Joel, I am currently running PostgreSQL v8.0.0 using RedHat Enterprise Linux v3 on a Dell with '2 PROCESSOR, 80532K, 2.8GHZ, 512K, 533, DECISION ONE'. So far so good. :-) Kind Regards, Keith ---(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] hardware mod based on feedback from the list
Joel Fradkin wrote: I was told the Xeon processors will do fine up to 64gig. I realize the 64bit chips may be faster, but it is also new and I feel safer with existing technologies and hardware vendors. My understanding is that CPU (4 Xeon 3gig processors) will not be a issue, but hopefully adding the additional drive systems will help the most for IO is what I am told is the big issue and hopefully utilizing the recommendation will help minimize the bottleneck. I believe there may well be an issue with multiple Xeon's - check the mailing list archives for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Function Doubt
Wilton wrote: StrCampo = ''new.'' || qCampos.attname; StrInsert = StrInsert || virgula || StrCampo || '' - ''; End Loop; Return new; End; What i need to do for the variable StrCampo returns to me "SP", "RJ", i mean, return a value inserted into the field. I do not want to use static command new.campo1, new.campo2 Ah - you can't. Not in plpgsql, anyway. You should perhaps look at pltcl or plperl. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] hardware mod based on feedback from the list
Thanks so much to everyone who jumped in both on a config and now the hardware side of things. I did modify my order on the new equipment to include a powervault 220 (just a bunch of drives) I added a controller card and 4 10 k drives for data raid 10 and 2 15 k drives raid 1 for WAL. I will use the array (internal to the 6650) that the OS is on for the syslog. I was told the Xeon processors will do fine up to 64gig. I realize the 64bit chips may be faster, but it is also new and I feel safer with existing technologies and hardware vendors. My understanding is that CPU (4 Xeon 3gig processors) will not be a issue, but hopefully adding the additional drive systems will help the most for IO is what I am told is the big issue and hopefully utilizing the recommendation will help minimize the bottleneck. Joel Fradkin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Function Doubt
Inside a function of trigger type, i have: Declare qCampos record; StrCampo string; StrInsert string; Begin StrCampo = ; StrInsert = ; For qCampos in select pga.attname from pg_class pgc, pg_attribute pga where pgc.relname = || tg_relname || and pgc.oid = pga.attrelid and pga.attnum > 0 and attisdropped <> ''t'' Loop StrCampo = ''new.'' || qCampos.attname; StrInsert = StrInsert || virgula || StrCampo || '' - ''; End Loop; Return new; End; What i need to do for the variable StrCampo returns to me "SP", "RJ", i mean, return a value inserted into the field. I do not want to use static command new.campo1, new.campo2 Thanks, Wilton Ruffato Wonrath -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.6 - Release Date: 27/1/2005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What's the equivalent in PL/pgSQL
That's bad. Is there really no ohter way? So it takes TWO termoral tables. Or even more? And it can't be just sql because theres more around that statement. > -Original Message- > From: Sean Davis [mailto:[EMAIL PROTECTED] > Sent: Donnerstag, 27. Jänner 2005 14:57 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] What's the equivalent in PL/pgSQL > > > See this section of the manual: > > http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- > structures.html > > In particular, look at 35.7.1.2 and 35.7.4. I think you need > to loop > through the results of the query in order to return them. If > you just > want to return the result set as a whole to another function > that needs > to work with the data, you can use a cursor. > > Sean > > On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: > > > Hi, > > I'm trying to find an equivalent plpgsql function as this: > > > > func x returns SETOF "Tablename" AS > > ' > > Select * from "Tablename"; > > ' language sql > > > > > > How is this accomplished with plpgsql while not using a loop or a > > second and > > third temporal table? > > > > ---(end of > > broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] URL activation through trigger
Sandeep, Using pl/perl at least, you could probably do this. My guess is that pl/python (and perhaps pl/R) would offer similar functionality. At least for pl/perl, I think you would need to use the untrusted version and use a module like LWP. I haven't tried this, but I think it should be possible (and fairly easy to do). Perhaps others can add some more detail. Sean On Jan 27, 2005, at 9:38 AM, Sandeep Gaikwad wrote: Hi, I am using Postgres 7.3.4. I want to write a trigger for insert, update, delete operation on say, user table. This trigger will activate an URL. URL will be as: http://sandeep/Trigger?userid=4&name=sandeep where, userid and password will be parameters to send. They may taken from database say, user table. Is this possible ? How can I do that ? Thanks & Regards, Sandeep - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Complete instruction in a trigger
Wilton <[EMAIL PROTECTED]> writes: > I have a table "customers", then a user go there and change the customer > record, for example he changes the zip code column. Then I can know that > something changed into the "customer table", and send a full update of > that record through the web to the database that i want, but i need to > know what column had changed to send a update just for that column. But > i do not know in which column his has made that change. So, i need to > know the instruction to do this. Something like a specific update for > that column modified by the user. People ask for this often, and in every case I've seen, they would be wrong to depend on it if they had it. The reason is that the original SQL query is not sufficient information to tell which columns changed. Consider for example the possibility that the query was rewritten by a rule, or the new tuple was modified by another trigger that executed ahead of yours. The only correct way to determine which columns changed is to compare the OLD and NEW values of each column ... and you'd better do it in an AFTER trigger, not a BEFORE trigger, if you want to be certain that you are looking at the final result. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Hardware for best performance was same question little different test MSSQL vrs Postgres
Subject: RE: [SQL] same question little different test MSSQL vrs Postgres Now you tell me. We had a fellow working here kept screaming AMD, but I am a very paranoid person and was not aware Linux and Postgres have been running on the new chips. I don't like to be a first. We have bought the Dell and I cant tell you if the controller uses 64bits, I just got what they had on their page for their 4 proc rack mount. Part of my reason for going Dell was we already have Dell equipment and the Linux support is offered from Dell as well, so I have one vendor to worry about. Being a developer and Director of IT I want the fastest best, but sometimes I flavor my opinions with safest and easiest. The RPM delivery is something I understand (it's easy). What is SU like? Is there any difference in the performance between the two Vendors? I am sure we will be buying more Postgres servers in the near future (One of the big reasons we are taking the time to convert from MSSQL was so we could afford to invest in more servers MSSQL was cost prohibitive even for one server). As easy as Fedura was I still had several issues getting to where I am now, so I am paranoid of something that requires even more knowledge to pull it off; that being said I never minded getting into the details to get a better end result. As you said we have made the investment in the Dell (25K). I feel pretty stupid if it is as you say a waste of money to get 8 gigs on this platform as I just made that same mistake a year ago when I bought the 2 processor boxes with standard addition MSSQL and 4 gigs (It only uses 2 gig). I was under the impression this machine would utilize all 8 gigs. Are you saying only 4 will be available for caching etc, or just the chipset cant deal with numbers 8 gig and will be slower to access them? If it is the later then I would imagine it would still outperform a similar box with 4 gig assuming my demand on cache is larger then 4 gig. Just to confirm you have these quad Opteron (I am assuming a 4 processor config?) in a production environment running su and postgres with hardware support from HP and software from su? You indicate three separate physical drives will give best performance (one for data 10K speeds, one for admin, one for wall 15 speed)? I am not too sophisticated at knowing how to irder this arrangement and set it up in Linux, any chance you could detail (1 card with 2 channels 4 10k drives on one channel, 2 15k drives on the second, do I need another channel and drive(s) for admin files?), drive layout when installing config in postgres to utilize? If need be maybe we can get you to do this as a consultant as I do understand how important the hardware and the proper config is. I found out too late with MSSQL that I should have used two seprate drive arrays, one for data, one for log (this would have required the split back plane). So not to plug a specific vendor but if you have production environment example with real equipment suggestions I would be very appreciative. I know that's a lot to ask so if you don't have time that's cool, thanks so much for bringing this up so that my next purchase I will seriously look at quad Opteron technology if it is a tried and true solution for this OS and Postgres. Joel Fradkin -Original Message- From: Andrew Hammond [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 5:16 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We've done some pretty extensive benchmarking and load testing on a couple of platforms including the Xeon and Opteron. You may have already bought that Dell box, but I'll say it anyway. Xeon quad processors are a terrible platform for postgres. Trying to use more than 4GB of memory on a 32 bit machine is a waste of money. If you want performance, get a quad Opteron with the same amount of memory. I guarantee you'll see at least an order of magnitude performance improvement and substantially more under highly concurrent loads. If you decide to go this way, HP sells a very nice box. I also strongly recommend you investigate SuSE instead of RedHat. Fedora core is good technology, but SuSE offers equally good technology with better support. Also make sure that your SCSI HBA is actually using the 64 bit PCI bus. There are cards out there which plug into 64 bit PCI but only actually address 32 bits (Qlogic's QLA2340 / 2342 for example). You make no mention of the disk subsystem you plan to use. This is most critical part of your system. Database performance is almost always bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10 array of disks you can manage. It's not worth spending the extra money to get 15kRPM disks for this. The size of the disks involved is pretty much irrelevant, only the number of them matters. Put the WAL files on a dedicated RAID 1 pair of 15kRPM disks. Put the postgre
[SQL] URL activation through trigger
Hi, I am using Postgres 7.3.4. I want to write a trigger for insert, update, delete operation on say, user table. This trigger will activate an URL. URL will be as: http://sandeep/Trigger?userid=4&name=sandeep where, userid and password will be parameters to send. They may taken from database say, user table. Is this possible ? How can I do that ? Thanks & Regards, Sandeep - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] same question little different test MSSQL vrs Postgres
Now you tell me. We had a fellow working here kept screaming AMD, but I am a very paranoid person and was not aware Linux and Postgres have been running on the new chips. I don't like to be a first. We have bought the Dell and I cant tell you if the controller uses 64bits, I just got what they had on their page for their 4 proc rack mount. Part of my reason for going Dell was we already have Dell equipment and the Linux support is offered from Dell as well, so I have one vendor to worry about. Being a developer and Director of IT I want the fastest best, but sometimes I flavor my opinions with safest and easiest. The RPM delivery is something I understand (it's easy). What is SU like? Is there any difference in the performance between the two Vendors? I am sure we will be buying more Postgres servers in the near future (One of the big reasons we are taking the time to convert from MSSQL was so we could afford to invest in more servers MSSQL was cost prohibitive even for one server). As easy as Fedura was I still had several issues getting to where I am now, so I am paranoid of something that requires even more knowledge to pull it off; that being said I never minded getting into the details to get a better end result. As you said we have made the investment in the Dell (25K). I feel pretty stupid if it is as you say a waste of money to get 8 gigs on this platform as I just made that same mistake a year ago when I bought the 2 processor boxes with standard addition MSSQL and 4 gigs (It only uses 2 gig). I was under the impression this machine would utilize all 8 gigs. Are you saying only 4 will be available for caching etc, or just the chipset cant deal with numbers 8 gig and will be slower to access them? If it is the later then I would imagine it would still outperform a similar box with 4 gig assuming my demand on cache is larger then 4 gig. Just to confirm you have these quad Opteron (I am assuming a 4 processor config?) in a production environment running su and postgres with hardware support from HP and software from su? You indicate three separate physical drives will give best performance (one for data 10K speeds, one for admin, one for wall 15 speed)? I am not too sophisticated at knowing how to irder this arrangement and set it up in Linux, any chance you could detail (1 card with 2 channels 4 10k drives on one channel, 2 15k drives on the second, do I need another channel and drive(s) for admin files?), drive layout when installing config in postgres to utilize? If need be maybe we can get you to do this as a consultant as I do understand how important the hardware and the proper config is. I found out too late with MSSQL that I should have used two seprate drive arrays, one for data, one for log (this would have required the split back plane). So not to plug a specific vendor but if you have production environment example with real equipment suggestions I would be very appreciative. I know that's a lot to ask so if you don't have time that's cool, thanks so much for bringing this up so that my next purchase I will seriously look at quad Opteron technology if it is a tried and true solution for this OS and Postgres. Joel Fradkin -Original Message- From: Andrew Hammond [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 5:16 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We've done some pretty extensive benchmarking and load testing on a couple of platforms including the Xeon and Opteron. You may have already bought that Dell box, but I'll say it anyway. Xeon quad processors are a terrible platform for postgres. Trying to use more than 4GB of memory on a 32 bit machine is a waste of money. If you want performance, get a quad Opteron with the same amount of memory. I guarantee you'll see at least an order of magnitude performance improvement and substantially more under highly concurrent loads. If you decide to go this way, HP sells a very nice box. I also strongly recommend you investigate SuSE instead of RedHat. Fedora core is good technology, but SuSE offers equally good technology with better support. Also make sure that your SCSI HBA is actually using the 64 bit PCI bus. There are cards out there which plug into 64 bit PCI but only actually address 32 bits (Qlogic's QLA2340 / 2342 for example). You make no mention of the disk subsystem you plan to use. This is most critical part of your system. Database performance is almost always bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10 array of disks you can manage. It's not worth spending the extra money to get 15kRPM disks for this. The size of the disks involved is pretty much irrelevant, only the number of them matters. Put the WAL files on a dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or syslog) on a seperate filesystem. - -- Andrew Hammond4
Re: [SQL] [GENERAL] Foreign Key relationship between two databases
On Thu, 27 Jan 2005 18:47:44 +0530, Sandeep Gaikwad <[EMAIL PROTECTED]> wrote: > Hi, > I am using postgres 7.3.4. I am new to postgres. > I can give foreign key relationship between two tables of same > database. Can I give foreign key relationship between tables of two databases > ? Plz, let me know if possible & send me how can I do that? Well... what you can do is replicate such table between two pg databases using Slony-I and then use foreign key on this replicated table. Regards, Dawid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] What's the equivalent in PL/pgSQL
See this section of the manual: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html In particular, look at 35.7.1.2 and 35.7.4. I think you need to loop through the results of the query in order to return them. If you just want to return the result set as a whole to another function that needs to work with the data, you can use a cursor. Sean On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Foreign Key relationship between two databases
On Thu, 27 Jan 2005, Sandeep Gaikwad wrote: > I can give foreign key relationship between two tables of same > database. Can I give foreign key relationship between tables of two > databases ? Plz, let me know if possible & send me how can I do that? Unfortunately, that's not really currently possible. You might be able to fake some portion of it with custom triggers using dblink but I don't think the locking would entirely work. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Foreign Key relationship between two databases
Hi, I am using postgres 7.3.4. I am new to postgres. I can give foreign key relationship between two tables of same database. Can I give foreign key relationship between tables of two databases ? Plz, let me know if possible & send me how can I do that? Thanks & Regards, Sandeep. - Disclaimer: The contents of this message are confidential and intended to the addressee at the specified e-mail address only. Its contents may not be copied or disclosed to anyone other than the intended recipient. If this e-mail is received in error, please contact Vertex Software Pvt. Ltd immediately on +91 20 4041500 with details of the sender and addressee and delete the e-mail. Vertex Software Pvt. Ltd accepts no responsibility in the event that the onward transmission, opening or use of this message and/or any attachments adversely affects the recipient's systems or data. It is the recipient's responsibility to carry out such virus and other checks as the recipient considers appropriate. - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Complete instruction in a trigger
Sorry if i´m sendind this question to the wrong place... it´s because i´m new here and i didn´t found the place in the web to send my question over the same that i already did. But let´s see if you can help me. The situation is like this. I have a table "customers", then a user go there and change the customer record, for example he changes the zip code column. Then I can know that something changed into the "customer table", and send a full update of that record through the web to the database that i want, but i need to know what column had changed to send a update just for that column. But i do not know in which column his has made that change. So, i need to know the instruction to do this. Something like a specific update for that column modified by the user. Thanks very much. Wilton Ruffato Wonrath No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.6 - Release Date: 27/1/2005 ---(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
[SQL] What's the equivalent in PL/pgSQL
Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Constraint on 2 column possible?
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <[EMAIL PROTECTED]> wrote: > > Hi, > > I have a table: > CREATE TABLE werke1( > id SERIAL, > id_hr int4 NOT NULL, > id_wk int4 NOT NULL > ); > > CREATE TABLE contact( > id SERIAL, > type varchar(20), > ); > > > Now id_hr and id_wk are all referencing the same table contact(id). In the > contact table I have another column called type. > How can I write a constraint that checks that id_hr references contact(id) > and the contact(type='t1') > and that id_wk references contact(id) and the contact(type='t2'). If I understand what you want, you can do this with a multi-column foreign key and check constraints. CREATE TABLE werke1 ( id SERIAL, id_hr NOT NULL, hr_contact NOT NULL CHECK (hr_contact = 't1'), id_wk int4 NOT NULL, wk_contact NOT NULL CHECK (hr_contact = 't2'), CONSTRAINT werke1_hr FOREIGN KEY (id_hr,hr_contact) references contact(id,type), CONSTRAINT werke1_wk FOREIGN KEY (id_wk, wk_contact) references contact(id,type) ); This will cause the FKEY to match only contact entries that have the correct combination of id and type. Hope that helps! > > More explicit: the id_hr shows to the id from contact, and this line from > contact must have the line type='t1'. The same for id_wk just the type is > another. > > I can write: > CREATE TABLE werke1( > id SERIAL, > id_hr int4 NOT NULL references contact(id), > id_wk int4 NOT NULL references contact(id) > ); > but how do I check also the type column? > > Best regards, > Andy. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Complete instruction in a trigger
Wilton wrote: Hi... People, i wanna know how can i know inside of a function whar the trigger called, i mean which instruction SQL it called. I do not wanna know if it´s a insert, update or delete (tp_op), i need to know the complete instruction. For example, i need to know something like this: update phones set number = '-'; Do you know if maybe exists a tg_stantement? No, and the situation isn't quite as simple as that. Think about foreign-keys with a cascading update. What precisely are you trying to do? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Constraint on 2 column possible?
On Jan 27, 2005, at 6:44 AM, Andrei Bintintan wrote: Hi, I have a table: CREATE TABLE werke1( id SERIAL, id_hr int4 NOT NULL, id_wk int4 NOT NULL ); CREATE TABLE contact( id SERIAL, type varchar(20), ); It looks like you want a two-column primary key for table contact and then you can reference contact(id,type). Sean Now id_hr and id_wk are all referencing the same table contact(id). In the contact table I have another column called type. How can I write a constraint that checks that id_hr references contact(id) and the contact(type='t1') and that id_wk references contact(id) and the contact(type='t2'). More explicit: the id_hr shows to the id from contact, and this line from contact must have the line type='t1'. The same for id_wk just the type is another. I can write: CREATE TABLE werke1( id SERIAL, id_hr int4 NOT NULL references contact(id), id_wk int4 NOT NULL references contact(id) ); but how do I check also the type column? Best regards, Andy. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Constraint on 2 column possible?
Hi, I have a table:CREATE TABLE werke1(id SERIAL,id_hr int4 NOT NULL,id_wk int4 NOT NULL); CREATE TABLE contact(id SERIAL,type varchar(20), ); Now id_hr and id_wk are all referencing the same table contact(id). In the contact table I have another column called type. How can I write a constraint that checks that id_hr references contact(id) and the contact(type='t1') and that id_wk references contact(id) and the contact(type='t2'). More explicit: the id_hr shows to the id from contact, and this line from contact must have the line type='t1'. The same for id_wk just the type is another. I can write: CREATE TABLE werke1(id SERIAL,id_hr int4 NOT NULL references contact(id),id_wk int4 NOT NULL references contact(id)); but how do I check also the type column? Best regards, Andy.
[SQL] Complete instruction in a trigger
Hi... People, i wanna know how can i know inside of a function whar the trigger called, i mean which instruction SQL it called. I do not wanna know if it´s a insert, update or delete (tp_op), i need to know the complete instruction. For example, i need to know something like this: update phones set number = '-'; Do you know if maybe exists a tg_stantement? Thanks very much... see ya -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.4 - Release Date: 25/1/2005 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
