[SQL] Procedures in views
Does anyone know if the following is possible: Table A has a list of names with a column entry containing the name of an assoc pl/pgsql function Table B has a list of values Table C is a junction table btw A and B I would like to create a view that returns info from both tables and does a calc on some of the values in B based on the function name stored in A. Can this be done? If so, can someone point me in the right direction for documnetation. Thanks, Morgan ---(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
[SQL] Query Approach and performance
Hey everyone, On average, are multiple simple queries better performance-wise than joins? i.e. select A.col1 from table1 A select B.col2 from table2 B where B.col1 = A.col1 etc vs select A.col1, B.col2 from table1 A, table2 B where B.col1 = A.col1 Are joins better for small/large numbers of tables? Is there a diff? My approach to date has been to keep queries as simple as possible, and when I see a need for complicated joins, I create a view and then do simple queries against that. Does pg cache queries like Oracle does so that repeated queries don't need to go through the compile phase and run faster? Is this configurable? Thanks, Morgan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem with n to n relation
just get rid of the serial_id in person2adress -- there is no reason for it. Make the pk of that table a composite --> person_id, address_id <-- that way you have added some additional integrity to your structure. Only one record can exist ffor a given person at a given address. However any person can have any number of address and any address can have any number of people living at it. Morgan At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: >Hi, > >i create n to n relations like this, right? > >create table person ( > idserial, > name text >); > >create table address ( > id serial, > street text > ... >); > >create table person2adress ( > id serial, > person_id integer not null references person(id), > address_id integer not null references address(id), >); > >than i can select all adresses from one person with id =1 with >select street >from address >where id = > ( > select adress_id > from person2adress > where person_id = 1 > ); > >ok so far so good. but you can still insert persons without any >adress. so its a 0..n relation. But how van i achieve that you can´t >insert any person without adress??? > >thanks in advance >janning > >-------(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster - Morgan Curley Partner, e4media [EMAIL PROTECTED] 917 751 8328 http://www.e4media.com - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
