[SQL] tutorials for complex.sql & complex.c
hi can anyone inform me where to get the postgreSQL complex.sql & complex.c tutorials from, cos i have no idea. in the 7.4.2-A4 doc it says: The examples in this section can be found in complex.sql and complex.c in the src/tutorial directory of the source distribution. See the README file in that directory for instructions about running the examples. im still unsure, where to look. can anyone solve my prob? -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] tutorials for complex.sql & complex.c
Andrew Thorley wrote: The examples in this section can be found in complex.sql and complex.c in the src/tutorial directory of the source distribution. See the README file in that directory for instructions about running the examples. im still unsure, where to look. can anyone solve my prob? In the source distribution (.tar.gz or .bz2) - the one you'd use to compile PostgreSQL from scratch. Untar it, and you'll see src/tutorial directories. -- 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] Querying a list field against another list
Hi, I tried to mail this to the novice list I believe it was rejected: >> The original message was received at 2004-11-26 14:55:09 +0100 from postoffice.local [10.0.0.1] - The following addresses had permanent fatal errors - <[EMAIL PROTECTED]> -Transcript of session follows - ... while talking to postoffice.local.: >>> RCPT To:<[EMAIL PROTECTED]> <<< 550 5.1.1 unknown or illegal alias: [EMAIL PROTECTED] 550 <[EMAIL PROTECTED]>... User unknown >> So here's my question. Hi people, This is not quite a pg question, but any suggestions are most welcome. How can one query a list of values against a db field that contains a list of values ? Table foo foo_id | foo_name | da_list -- 1 | x | 1,2,3,4,5 2 | y | 1,4,5 3 | z | 4,5,11 4 | xyz | 14,15,33 As a result from another query I have parameter bar = '1,4' and want to find all rows from foo where da_list contains '1' or '4'. So loop over bar to loop over da_list in foo ? My humble thanks, Aarni -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Querying a list field against another list
On Fri, Nov 26, 2004 at 16:17:57 +0200, Aarni Ruuhimäki <[EMAIL PROTECTED]> wrote: > Hi, > > I tried to mail this to the novice list I believe it was rejected: No you weren't. The mail server that is reporting that error is sending the response to the wrong place. It is supposed to be going to the envelope sender address, not the address in the from header. The message only indicates that that one address didn't get a copy of your message. > > >> > The original message was received at 2004-11-26 14:55:09 +0100 > from postoffice.local [10.0.0.1] > > - The following addresses had permanent fatal errors - > <[EMAIL PROTECTED]> > > -Transcript of session follows - > ... while talking to postoffice.local.: > >>> RCPT To:<[EMAIL PROTECTED]> > <<< 550 5.1.1 unknown or illegal alias: [EMAIL PROTECTED] > 550 <[EMAIL PROTECTED]>... User unknown > >> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Type Inheritance
"Andrew Thorley" <[EMAIL PROTECTED]> writes: > Does anyone know how to implement type inheritance in postgresql? in oracle > you just use the word UNDER in ur code i.e: > CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT); If you had said what this *does*, we might be better able to help. But take a look at CREATE DOMAIN, as well as the INHERITS and LIKE options in CREATE TABLE. Some part of that might be close to what you are looking for. 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] Way to stop recursion?
Been banging my head against the wall for days and starting to think there is no way to do what I need. Hoping someone on here can prove me wrong. UPDATE rules work perfectly for what I need to do except I need them to only run once, not try and recurse (which of course isn't allowedby postgresql anyway). Triggers seem a less efficient way to do the same thing, though I understand they would run recursively too. Here's the table structure in question: CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); INSERT INTO parent VALUES(1, 'adult', 0); INSERT INTO child VALUES(1, 1, 'kid 1', 0); INSERT INTO child VALUES(2, 1, 'kid 2', 0); What I need, is when "common" is changed for a parent, then that new value is reflected in "common" for all the children, ie: UPDATE parent SET cola='something', common=1 WHERE id=1; That in itself is no problem: CREATE RULE update_child_common AS ON UPDATE TO parent WHERE NEW.common!=OLD.common DO UPDATE child SET common=NEW.common WHERE parent_id=OLD.id; Problem is, when "common" is changed for a child, I need the parent and all siblings to reflect that value too, ie: UPDATE child SET cola='some value',common=2 WHERE id=2; If I could force recursion off, I could do that with: CREATE RULE update_common_from_child AS ON UPDATE TO child WHERE NEW.common!=OLD.common DO (UPDATE parent SET common=NEW.common WHERE id=NEW.parent_id;UPDATE child SET common=NEW.common WHERE parent_id=NEW.parent_id) As it stands, I can not find a way to do that. Any variation I try (using "flags", using INSTEAD, triggers) has led to recursion protection kicking in and postgresql refusing to run the query. I want to stay away from triggers if I can as I imagine they must be significantly less efficient when updating large numbers of parents and/or children at once (which happens frequently in the application), assuming a trigger could be made to do what I need at all. Hoping I'm missing something obvious... - Jonathan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Way to stop recursion?
Jonathan Knopp <[EMAIL PROTECTED]> writes: > CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); > CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); > What I need, is when "common" is changed for a parent, then that new > value is reflected in "common" for all the children, ie: > ... > Problem is, when "common" is changed for a child, I need the parent and > all siblings to reflect that value too, ie: Seems to me that your real problem is a bogus database layout. If there should only be one "common" value for a parent and children, then only store one value ... that is, "common" should exist only in the parent. You can if you like make a view that emulates the appearance of a child table with a common column, viz create view childview as select child.*, parent.common from child, parent where parent_id = parent.id; and it would even be possible to make a rule that allows updating this view. 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
Re: [SQL] Way to stop recursion?
On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote: > UPDATE rules work perfectly for what I need to do except I need them to > only run once, not try and recurse (which of course isn't allowedby > postgresql anyway). Triggers seem a less efficient way to do the same > thing, though I understand they would run recursively too. Here's the > table structure in question: You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Way to stop recursion?
On Fri, Nov 26, 2004 at 04:31:11PM -0500, Tom Lane wrote: > > Seems to me that your real problem is a bogus database layout. If there > should only be one "common" value for a parent and children, then only > store one value ... that is, "common" should exist only in the parent. Tom's answers always make me realise that I should think harder before I talk. He's right, of course: one common value means store it once. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Way to stop recursion?
Tom Lane wrote: CREATE TABLE parent (id INT, cola CHAR(1), common CHAR(1)); CREATE TABLE child (id INT, parent_id INT, cola(1), common(1)); What I need, is when "common" is changed for a parent, then that new value is reflected in "common" for all the children, ie: ... Problem is, when "common" is changed for a child, I need the parent and all siblings to reflect that value too, ie: Seems to me that your real problem is a bogus database layout. If there should only be one "common" value for a parent and children, then only store one value ... that is, "common" should exist only in the parent. ... Sorry, I should have mentioned that there is a lot more to the design that makes this replication necessary, including another two levels to the tree plus the ability to have orphaned children. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Way to stop recursion?
You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing. True, but you can get out of the hole in another way : - Change the name of your table to "hidden_table" - Create a view which is a duplicate of your table : CREATE VIEW visible_table AS SELECT * FROM hidden_table; -> Your application now accesses its data without realizing it goes through a view. Now create a rule on this view, to make it update the real hidden_table. As the rule does not apply to hidden_table, it won't recurse. Other solution (this similar to what Tom Lane proposed I think) : Create a field common_id in your table, with - an insert trigger which puts a SERIAL default value if there is no parent, or copies the parent's value if there is one - an update trigger to copy the new parent's common_id whenever a child changes parent (if this ever occurs in your design) Now create another table linking common_id to the 'common' value. Create a view which joins the two, which emulates your current behaviour. Create an ON UPDATE rule to the view which just changes one row in the link table. If you do a lot of selects, solution #1 will be faster, if you do a lot of updates, #2 will win... Just out of curiosity, what is this for ? On Fri, 26 Nov 2004 16:34:48 -0500, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote: UPDATE rules work perfectly for what I need to do except I need them to only run once, not try and recurse (which of course isn't allowedby postgresql anyway). Triggers seem a less efficient way to do the same thing, though I understand they would run recursively too. Here's the table structure in question: You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing. A ---(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] Way to stop recursion?
Pierre-Frédéric Caillaud wrote: - Change the name of your table to "hidden_table" - Create a view which is a duplicate of your table : CREATE VIEW visible_table AS SELECT * FROM hidden_table; -> Your application now accesses its data without realizing it goes through a view. Now create a rule on this view, to make it update the real hidden_table. As the rule does not apply to hidden_table, it won't recurse. Other solution (this similar to what Tom Lane proposed I think) : Create a field common_id in your table, with - an insert trigger which puts a SERIAL default value if there is no parent, or copies the parent's value if there is one - an update trigger to copy the new parent's common_id whenever a child changes parent (if this ever occurs in your design) Now create another table linking common_id to the 'common' value. Create a view which joins the two, which emulates your current behaviour. Create an ON UPDATE rule to the view which just changes one row in the link table. If you do a lot of selects, solution #1 will be faster, if you do a lot of updates, #2 will win... The "hidden table" method should work just fine. Ingenius idea, thank you! Just out of curiosity, what is this for ? The actual application has companies instead of parents, employees instead of children, then emails as children of employees and/or companies, and folders as parents of companies and employees. The "common" field (in all 4 layers) are a pair of permissions flags. May I humbly suggest two possible todo's for postgreSQL: a simple flag to suppress recursion (easier/more powerful way of doing the above), and/or more direct access to query rewriting. Seems right now rules require you to rewrite queries while partially blind to them. Being able to rewrite queries in statement triggers similar to what can be done with row triggers would be very nice too. ---(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
