[SQL] how many JOINs?
Hello List! Do you know how many times can JOIN be used in a query? How many JOINs does PostgreSQL support in ONE query? ---(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] Type Inheritance
hi tom thanks for reply. im trying to provide inheritance for data types (domains), not tables. i will look into the syntax associated with domains as data types does not include an inheritance function (or so i think). regards andy. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Andrew Thorley" <[EMAIL PROTECTED]> Subject: Re: [SQL] Type Inheritance Date: Fri, 26 Nov 2004 13:14:07 -0500 > > "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 > -- __ 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 8: explain analyze is your friend
Re: [SQL] Type Inheritance
"If you had said what this *does*, we might be better able to help" basically i have a series of types (or data types), of which one is the root and each of the other types appear under the root as in generalisation/specialisation style, inheriting the above types attributes etc. this is what im trying to achieve, although at present, to no avail :( - Original Message - From: "Andrew Thorley" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Subject: Re: [SQL] Type Inheritance Date: Sat, 27 Nov 2004 22:04:55 +0800 > > hi tom thanks for reply. > > im trying to provide inheritance for data types (domains), not tables. i will > look into the syntax associated with domains as data types does not include > an inheritance function (or so i think). > > regards andy. > > > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "Andrew Thorley" <[EMAIL PROTECTED]> > Subject: Re: [SQL] Type Inheritance > Date: Fri, 26 Nov 2004 13:14:07 -0500 > > > > > "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 > > > > -- > __ > 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 8: explain analyze is your friend > -- __ 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] Querying a list field against another list
Try this: CREATE OR REPLACE FUNCTION csv_matches(TEXT, TEXT) RETURNS BOOLEAN AS $$ DECLARE -- $1 is the field text, $2 is the list of ints to try and match. m TEXT; f TEXT; i INTEGER := 1; j INTEGER; BEGIN IF $1 IS NULL THEN RETURN 'f'; ELSIF $2 IS NULL THEN RETURN 'f'; END IF; LOOP m := split_part($2, ',', i); IF m LIKE '' THEN RETURN 'f'; END IF; j := 1; LOOP f := split_part($1, ',', j); IF f LIKE '' THEN EXIT; END IF; IF f LIKE m THEN RETURN 't'; END IF; j := j + 1; END LOOP; i = i + 1; END LOOP; END; $$ LANGUAGE 'plpgsql'; Then you can do "select * from foo where csv_matches(da_list, '1,4');" -Mark. Aarni Ruuhimäki wrote: 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]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] update/insert data
Hi All, I have two tables in different schemas. The first table in the data_transfer schema is loaded with a COPY command. I need to transfer the data to the second schema inserting new records and updating existing records. What is the best way to achieve this functionality? Kind Regards, Keith __ 99main Internet Services http://www.99main.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
