Re: [GENERAL] Simulating an outer join
Bruce Momjian wrote: I am just writing the EXISTS section from my book. I don't think it matters what fields are returned from an EXISTS subquery. If I am wrong, someone please let me know. Celko also writes (in his chapter on EXISTS in "SQL for Smarties"): "In general the SELECT * option should perform better than the actual column. It lets the query optimizer decide which column to use. If a column has an index on it, then simply seeing a pointer to the index is enough to determine that something exists." Obviously you're in a much better position than me to judge whether that's the case in pgsql! But it might be worth a test. Julian Scarfe
Re: [GENERAL] PSQL Function() help....
Hello, Sure you can use arrays as returned results in your function. regards. Omid Omoomi From: Peter Eisentraut [EMAIL PROTECTED] To: Dale Anderson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] PSQL Function() help Date: Wed, 12 Jan 2000 04:58:53 +0100 On 2000-01-12, Dale Anderson mentioned: Here is what I am trying to do. I an trying to create a function that is passed two numbers, get_people(2000,1). The first value is a year, and the second is a week. What I want the function to do, is to select all the names from a name table, and return a list of names that don't have an entry in the data table for the year, and week specified. Can this be done?? No. Functions cannot return result sets. Certainly a deficiency, but nobody is perfect. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden __ Get Your Private, Free Email at http://www.hotmail.com
[GENERAL] Confussion with table-lock levels and isolation levels
Hi fellows ! I would only want to ask some questions concerning table-locking levels and isolation levels: * First of all: should I assume that AccessXXX modes imply locking the complete table and Row imply locking only the rows which have been accessed ? Will then the conflicts be solved according the hierarchy between modes ? * Second: - What does exactly mean that a mode 'CONFLICTS' with another ? - Does it mean that another concurrent transactions having these modes will have to wait until the first transaction have finished (commit or roll back) ? - Can we determine (when accessing a row in a table) wether we will have a conflict or not according to the criteria explained in the previous question (Access- complete table, Row - rows accessed) ? *Third: If all the previous assumptions are true: - When there is a conflict, will the only consequence be that all concurrent transactions will be processed in a FIFO serie and not in parallel ? - What about all the others concurrent transactions which haven't conflicted ? How can you avoid falling into contradiction with the isolation level (and assure the protection against non-repeteable reads or phantom reads ? - I feel those two levels (transactions and isolation levels) are two layers so the transactions will be processed according to a FIFO serie when exist any problem concerning the isolation level or the table-locking. Is this a good way to describe the way PostgreSQL manages the things ? Thank you very much for your help. By the way, is the first time i'm subscribed to a mailing list so if I do anything inappropiate or strange please tell me. Best regards Gabi :-)
Re: [GENERAL] How do you live without OUTER joins?
Sarah Officer wrote: How about using a union? select cc.com_cat_long, co.company_name from company_category cc, company co where cc.com_cat_abbr = co.com_cat_abbr union select 'unknown' as com_cat_long, company_name from company where com_cat_abbr is null; Yes, would be the best way to go. Unfortunately I need one of this outer joins in a VIEW, and seems to me that a VIEW cannot be created with a UNION. Therefore, for one of our projects we had to setup an intermediate table kept consistent using a number of triggers. Having outer joins or UNION in VIEWs would definitively be a much better way! -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://www.sevenseas.org/~alessio Nicosia, Cyprus phone: +357-2-750652 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Re: [GENERAL] Simulating an outer join
Bruce Momjian wrote: I am just writing the EXISTS section from my book. I don't think it matters what fields are returned from an EXISTS subquery. If I am wrong, someone please let me know. Celko also writes (in his chapter on EXISTS in "SQL for Smarties"): "In general the SELECT * option should perform better than the actual column. It lets the query optimizer decide which column to use. If a column has an index on it, then simply seeing a pointer to the index is enough to determine that something exists." Obviously you're in a much better position than me to judge whether that's the case in pgsql! But it might be worth a test. In psql, I think * would generate all the columns, then throw it away, while a specific column would only carry around that column in the subquery result. so a single column is better. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
