[SQL] RE: Joining several tables
1) Select t1.* from table_1 t1, table2 t2 where t1.column = t2.column; 2) Select t1.* from table_1 t1 join table2 t2 on t1.column = t2.column; -Original Message- From: Stephan Richter [SMTP:[EMAIL PROTECTED]] Sent: Sunday, January 21, 2001 11:20 PM To: [EMAIL PROTECTED] Subject:Joining several tables Hello, I have the following situation: I have an Address, PhoneNumber and Contact table all having a contactid in common, but sometimes the phone number does not exist. I want to join these three tables. How do I do that? I could not find the syntax for multiple tables... Regards, Stephan -- Stephan Richter CBU - Physics and Chemistry Student Web2k - Web Design/Development & Technical Project Management
Re: [SQL] unreferenced primary keys: garbage collection
Jan Wieck wrote: > Forest Wilkinson wrote: > > I have a database in which five separate tables may (or may not) reference > > any given row in a table of postal addresses. I am using the primary / > > foreign key support in postgres 7 to represent these references. > > > > My problem is that, any time a reference is removed (either by deleting or > > updating a row in one of the five referencing tables), no garbage > > collection is being performed on the address table. That is, when the > > last reference to an address record goes away, the record is not removed > > from the address table. Over time, my database will fill up with > > abandoned address records. > > While this behaviour makes sense in your case, it's not > subject to referential integrity constraints. You could > arrange for it with custom trigger procedures, checking all > the five tables on DELETE or UPDATE on one of them. > > I'll make up a little example and post it the other day - > need to take a nap now and tomorrow will be one of these > 30-hour days (from MET to EST), so don't expect anything > before Monday afternoon (EST). Here it is: CREATE TABLE t_addr ( a_idinteger PRIMARY KEY, a_name text ); CREATE TABLE t_customer ( c_idinteger PRIMARY KEY, c_address integer REFERENCES t_addr ); CREATE TABLE t_order ( o_idinteger PRIMARY KEY, o_customer integer REFERENCES t_customer ON DELETE CASCADE, o_shipaddr integer REFERENCES t_addr ); CREATE FUNCTION tidy_up_addr(integer) RETURNS bool AS ' DECLARE chk_addrALIAS FOR $1; BEGIN -- -- Check if address is still referenced from t_customer -- IF count(c_address) > 0 FROM t_customer WHERE c_address = chk_addr THEN RETURN ''f''; END IF; -- -- Check if address is still referenced from t_order -- IF count(o_shipaddr) > 0 FROM t_order WHERE o_shipaddr = chk_addr THEN RETURN ''f''; END IF; -- -- Address not required any more - get rid of it. -- DELETE FROM t_addr WHERE a_id = chk_addr; RETURN ''t''; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION customer_upd_or_del() RETURNS opaque AS ' BEGIN PERFORM tidy_up_addr(old.c_address); RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER customer_upd_or_del AFTER UPDATE OR DELETE ON t_customer FOR EACH ROW EXECUTE PROCEDURE customer_upd_or_del(); CREATE FUNCTION order_upd_or_del() RETURNS opaque AS ' BEGIN PERFORM tidy_up_addr(old.o_shipaddr); RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER order_upd_or_del AFTER UPDATE OR DELETE ON t_order FOR EACH ROW EXECUTE PROCEDURE order_upd_or_del(); We have two tables referencing the address table. Each of get's it's own trigger, simply calling the tidy-up function that removes the address if it's not referenced any more. Thus, adding a 3rd referencing table to the schema needs to add the check for reference to one central function, plus a very simple trigger on the new table. Hope this works for you. Have fun, Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] Re: is there a mysql to postgresql sql converter?
FYI, 7.1 will have a MySQL dump converter in /contrib. [ Charset ISO-8859-1 unsupported, converting... ] > > On Sat, 18 Nov 2000 01:13:23 +0200, Max Fonin said: > > > Can give a link ? > > > > can do : > http://freshmeat.net/projects/mysql2pgsql/?highlight=convert+sql > > i havent tried their's > > did try yours > > it was nice to have the transactions! > > > > > On Fri, 17 Nov 2000 13:45:37 -0800 > > clayton cottingham <[EMAIL PROTECTED]> wrote: > > > > > Max Fonin wrote: > > > > > > > > Hi. > > > > > > > > I'm writing MySQL->Postgres dump converter. E.g. it will convert only >database dumps produced by mysqldump. > > > > It's almost ready, problems are ENUM and SET types. I have problems with >types emulation. > > > > Maybe someone help me guys ? > > > > > > > > Anyway, some half-working version attached. > > > > > > > > Max Rudensky. > > > > > > > > > > > >Name: my2pg.pl > > > >my2pg.plType: Perl Program (application/x-perl) > > > >Encoding: base64 > > > > > > > > > thats cool!! > > > > > > i thought i saw another converter in the last couple o months on > > > freshmeat too > > > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Image in database
Hello everyone, I have to create table which stores user_id and their photo(gif format). How to insert and get the photo of the users using Java Servlets? -- Ramesh HR Trainee Engineer EASi Technologies 213, 3rd Main, 4th Cross Chamrajpet, Bangalore - 560 018 India Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 Facsimile: 667 5274 www.easi.soft.net
Re: [SQL] pl/pgsql Limits
Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: > >> As it is known that any funtion, written in pl/pgsql, can only > >> retrun one tuple. I am just wondering it were true as well for function > >> written in C language. I need to write few function that will retrun > >> mulitiple rows satsifying a certain set of conditions. > > > We plan to tackle the problem for v7.2. > > It is possible for a C function to return a set (ie, multiple values > returned over successive calls) as of 7.1; it's even documented, see > src/backend/utils/fmgr/README. And you can return a tuple if you know > how (this part is not documented, but you can crib it from the > SQL-function support in backend/executor/functions.c). > > The real problem is that the rest of the system doesn't let you *do* > anything very useful with either set-valued or tuple-valued functions. > This is what we need to address in future releases. Ideally I think > a function returning sets and/or tuples should be treated as a table > source, so that you'd write ... FROM function(args) AS alias, ... > > regards, tom lane How do you work around this? All I can think of is to call a the function and have it create a temporary table, then select from that table in the same transaction (to use the same connection). Does a temporary table created by a function continue to exist after the function finishes? I have a stored procedure in SQL Server that I have to create a temporary table then select from the table inside the same procedure. I'm not sure it would work after the SP is done... Ian
Re: [SQL] pl/pgsql Limits
Tom Lane wrote: > Jan Wieck <[EMAIL PROTECTED]> writes: > >> As it is known that any funtion, written in pl/pgsql, can only > >> retrun one tuple. I am just wondering it were true as well for function > >> written in C language. I need to write few function that will retrun > >> mulitiple rows satsifying a certain set of conditions. > > > We plan to tackle the problem for v7.2. > > It is possible for a C function to return a set (ie, multiple values > returned over successive calls) as of 7.1; it's even documented, see > src/backend/utils/fmgr/README. And you can return a tuple if you know > how (this part is not documented, but you can crib it from the > SQL-function support in backend/executor/functions.c). > > The real problem is that the rest of the system doesn't let you *do* > anything very useful with either set-valued or tuple-valued functions. > This is what we need to address in future releases. Ideally I think > a function returning sets and/or tuples should be treated as a table > source, so that you'd write ... FROM function(args) AS alias, ... > > regards, tom lane OK, here's my brute force stupid hack to work around it. I create a temporary table, call my procedure, which inserts records into my temporary table, then I select from it. The table had to exist when I created the procedure, but that's OK, I just delete it afterwards. When I disconnect with psql, the temp table goes away. Of course I could just drop it, but I am curious... With AOLServer, which uses connection pooling, might my connection stay open all day and other transactions have visibility of my temporary table? I don't quite get that part. Ian
[SQL] Re: [GENERAL] MySQL -> Postgres dump converter
FYI, we have a MySQL Perl script in 7.1beta. Would you please check that and see if you have any enhancements? Thanks. > Hi. > > MySQL->Postgres dump converter is now available at > http://ziet.zhitomir.ua/~fonin/code/my2pg.pl. Still beta and > bugsome version but working, supports MySQL ENUMs, near the end > are SET emulation. Please help me to test. > > Max Rudensky. > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
