Re: [SQL] Please help me to slove this SQL statements
-Message d'origine- De : Freshman [mailto:[EMAIL PROTECTED] Envoyé : jeudi 30 octobre 2003 13:38 À : [EMAIL PROTECTED] Objet : [SQL] Please help me to slove this SQL statements There are three table in database which is suppliers, projects, and shipments suppliers contain suppliers id, name ...etc projects contain project name ..suppliers ID ( J1---J7) ...etc shipments table contain suppliers ID , PROJECTS ID how can i query to find out the suppliers to supply all the projects ID I would suggest: SELECT * from suppliers WHERE NOT EXISTS( SELECT * FROM projects WHERE NOT EXISTS( SELECT * from shipments WHERE shipments.suppliers_ID = suppliers_suppliers_id AND shipments.project_ID = project.project_id ) ); so you select every supplier who didn't miss any project. Is that you want to do? ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Select and functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello all, I am having a little bit of trouble here. If I use the replace function in a select statement all works as expected. select firstname, lastname, replace(worktelephonenumber, ' ', '') AS worktel from contacts; But if I use it in a before trigger the procedure fails to work. CREATE OR REPLACE FUNCTION staging.write_work_country() RETURNS trigger AS ' DECLARE alias_rec RECORD; BEGIN -- Clean the phone number NEW.worktelephonenumber = REPLACE(NEW.worktelephonenumber,' ',''); RETURN NEW; END; ' I get the following error: ERROR: parser: parse error at or near "',''); Can anyone help me please? Thanks. Stephen. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQE/qQiKDAwPLUWQb6sRAhNpAJ0QdVzmYdhAhFVXf06v5zACuS3y9wCfSSlS W18AFuYb4J1le0W32uuIJto= =xe/b -END PGP SIGNATURE- ---(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] Select and functions
You need to use 2 single quotes: CREATE OR REPLACE FUNCTION staging.write_work_country() RETURNS trigger AS ' DECLARE alias_rec RECORD; BEGIN -- Clean the phone number NEW.worktelephonenumber = REPLACE(NEW.worktelephonenumber,'' '',); RETURN NEW; END; ' Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Stephen > J. Thompson > Sent: Wednesday, November 05, 2003 9:26 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Select and functions > > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hello all, > > I am having a little bit of trouble here. > > If I use the replace function in a select statement all works > as expected. > > select firstname, lastname, replace(worktelephonenumber, ' ', > '') AS worktel > from contacts; > > But if I use it in a before trigger the procedure fails to work. > > CREATE OR REPLACE FUNCTION staging.write_work_country() > RETURNS trigger AS > ' > DECLARE > alias_rec RECORD; > BEGIN > -- Clean the phone number > NEW.worktelephonenumber = > REPLACE(NEW.worktelephonenumber,' ',''); > RETURN NEW; > END; > ' > > I get the following error: > > ERROR: parser: parse error at or near "',''); > > Can anyone help me please? > > Thanks. > > Stephen. > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (GNU/Linux) > > iD8DBQE/qQiKDAwPLUWQb6sRAhNpAJ0QdVzmYdhAhFVXf06v5zACuS3y9wCfSSlS > W18AFuYb4J1le0W32uuIJto= > =xe/b > -END PGP SIGNATURE- > > > ---(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 > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Select and functions
--- "Stephen J. Thompson" <[EMAIL PROTECTED]> wrote: > But if I use it in a before trigger the procedure > fails to work. > > CREATE OR REPLACE FUNCTION > staging.write_work_country() > RETURNS trigger AS > ' > DECLARE > alias_rec RECORD; > BEGIN > -- Clean the phone number > NEW.worktelephonenumber = > REPLACE(NEW.worktelephonenumber,' ',''); > RETURN NEW; > END; > ' > > I get the following error: > > ERROR: parser: parse error at or near "',''); > > Can anyone help me please? Single quotes inside function bodies need to be escaped. The easiest way is to double them, i.e. from one single quote to two single quotes. Backslashes would work just as well (and also need to be escaped if used in a function body). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] avoid circular references
Hi there, I've got a table 'objtable' of objects which primary id is 'idobj' of type serial. objtable : idobj objname 1 nameone 2 nametwo 3 namethree ... Each object can contain one or many other objects from the same table, so I guess this relationship can be expressed with something like : CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL, containerid INTEGER REFERENCES objtable(idobj), contentid INTEGER REFERENCES objtable(idobj) ); What I would like is to be able to avoid circular references : - an object can't contain itself. - an object can't contain one of its containers. So I'd like to know how to create additionnal integrity constraints to solve this problem, and especially what syntax I should use. I thought about creating a rule but maybe the best is a trigger (I'm not sure I really understand the difference) if I see what happens when I create new tables. But what can I do in my trigger to have PostgreSQL understand there's an integrity violation ? Thanks in advance for any help on this. Jerome Alet -- "A non-free program is a predatory social system that keeps people in a state of domination and division, and uses the spoils to dominate more." - RMS ---(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] [HACKERS] Schema boggle...
Chris, > Monolithic tables have their uses, I don't and wont contest that, however > we've been using a monolithic structure in our current AMS distribution and > dataset. That and I'm more confident working on a 3D level, versus 2D, the > schema levels introduce the third dimension into my structure and simplify > visualizing how I want things to work. TMI! TMI! Ok, I believe that it works for you. You've made a tradeoff, though, and some operations that were simple in the old monolithic structure will now be more difficult -- however worthwhile the overall tradeoff may be. > resulting query as he suggested, I just need to learn plpgsql programming > so that I can work out all of the details (Tom, I might bug you about that > once in a while). Just go ahead and post your PL/pgSQL questions to the list. There are people on this list who've written thousands of lines of PL/pgSQL ... -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] avoid circular references
Jerome, > Each object can contain one or many other objects from the same > table, so I guess this relationship can be expressed with something > like : This is called a "tree structure". > CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL, > containerid INTEGER REFERENCES objtable(idobj), > contentid INTEGER REFERENCES objtable(idobj) ); This is overcomplicating things, unless a single object can belong to multiple containers, which would strike me as peculiar. Easier just to have a "containerid" in the objtable, which is your basic Proximity List. But of course, maybe I'm not understanding you and you do have multiple inheritance. > What I would like is to be able to avoid circular references : > > - an object can't contain itself. > > - an object can't contain one of its containers. > > So I'd like to know how to create additionnal integrity constraints > to solve this problem, and especially what syntax I should use. Best to use some kind of recursive function. I do this for a calendaring setup with event templates, where events can have multiple (possible) parents and multiple children. Just write a pl/pgSQL function which reverse-traces the parentage of the new object, looking for copies of itself. > I thought about creating a rule but maybe the best is a trigger > (I'm not sure I really understand the difference) if I see > what happens when I create new tables. But what can I do in > my trigger to have PostgreSQL understand there's an integrity > violation ? A trigger. Just use a BEFORE trigger and raise an exception if a self-parent is found. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] avoid circular references
On Wed, Nov 05, 2003 at 09:15:39PM -0800, Josh Berkus wrote: > Jerome, > > > Each object can contain one or many other objects from the same > > table, so I guess this relationship can be expressed with something > > like : > > This is called a "tree structure". Thanks ! > > CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL, > > containerid INTEGER REFERENCES objtable(idobj), > > contentid INTEGER REFERENCES objtable(idobj) ); > > This is overcomplicating things, unless a single object can belong to multiple > containers, which would strike me as peculiar. Easier just to have a > "containerid" in the objtable, which is your basic Proximity List. > > But of course, maybe I'm not understanding you and you do have multiple > inheritance. Yes an object could theorically belong to multiple containers, so that's not really a tree I suppose. > > What I would like is to be able to avoid circular references : > > > > - an object can't contain itself. > > > > - an object can't contain one of its containers. > > > > So I'd like to know how to create additionnal integrity constraints > > to solve this problem, and especially what syntax I should use. > > Best to use some kind of recursive function. I do this for a calendaring > setup with event templates, where events can have multiple (possible) parents > and multiple children. Just write a pl/pgSQL function which reverse-traces > the parentage of the new object, looking for copies of itself. Any example for this, especially on how to raise an integrity exception ? > > I thought about creating a rule but maybe the best is a trigger > > (I'm not sure I really understand the difference) if I see > > what happens when I create new tables. But what can I do in > > my trigger to have PostgreSQL understand there's an integrity > > violation ? > > A trigger. Just use a BEFORE trigger and raise an exception if a self-parent > is found. Thanks for your help ! Jerome Alet -- "A non-free program is a predatory social system that keeps people in a state of domination and division, and uses the spoils to dominate more." - RMS ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
