Thread

1. faster output from php and postgres

   Mukta Telang <mukta@darya.nio.org> — 2003-05-23T15:39:23Z

   Hi,
   I am dealing with a database with many-to-many relation and have a php
   script that joins these tables and displays the output.
   But the problem is that after entering some 50 records the output is
   very slow!
   I can count till 50 before the script produces the output!
   The complete description of the problem is written below..
   Thanks in advance,
   Mukta
   
   
   
   
   description in detail:
   
   
   database part:
   
   Three tables: paper,author and author_paper
   
   "A paper has many authors and an author has many papers."
   In this way there is many to many relation.
   
   The attributes of paper are: paper_id (primary key),title,journal,year
   etc
   
   The attributes of author are: author_id(primary key) and name
   
   The attributes of author_paper are: author_id ,paper_id and level
   ( author with level=1 is the main author of the paper )
   
   
   
   php part:
   
   For an author:
   1. get author_id from author table
   2. select all paper_ids from author_paper table
   3. For each of the above selected paper_ids:
           a. print all the attributes of paper
           b. select all authors of the paper and print their names in the
   order
   of their "level"
   
   

2. Re: faster output from php and postgres

   scott.marlowe <scott.marlowe@ihs.com> — 2003-05-23T16:04:09Z

   It's probably just a slow query.
   
   Could you print out your query in PHP to the web browser, then cut and 
   paste it into the psql monitor to get an explain anaylze output?
   
   i.e.
   psql dbname
   #> explain analyze select .... rest of query goes here;
   
   And give us the output of that?  Thanks.
   
   On Fri, 23 May 2003, Mukta Telang wrote:
   
   > Hi,
   > I am dealing with a database with many-to-many relation and have a php
   > script that joins these tables and displays the output.
   > But the problem is that after entering some 50 records the output is
   > very slow!
   > I can count till 50 before the script produces the output!
   > The complete description of the problem is written below..
   > Thanks in advance,
   > Mukta
   > 
   > 
   > 
   > 
   > description in detail:
   > 
   > 
   > database part:
   > 
   > Three tables: paper,author and author_paper
   > 
   > "A paper has many authors and an author has many papers."
   > In this way there is many to many relation.
   > 
   > The attributes of paper are: paper_id (primary key),title,journal,year
   > etc
   > 
   > The attributes of author are: author_id(primary key) and name
   > 
   > The attributes of author_paper are: author_id ,paper_id and level
   > ( author with level=1 is the main author of the paper )
   > 
   > 
   > 
   > php part:
   > 
   > For an author:
   > 1. get author_id from author table
   > 2. select all paper_ids from author_paper table
   > 3. For each of the above selected paper_ids:
   >         a. print all the attributes of paper
   >         b. select all authors of the paper and print their names in the
   > order
   > of their "level"
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
   > 
   
   
   

3. Re: faster output from php and postgres

   Frank Bax <fbax@sympatico.ca> — 2003-05-23T16:49:37Z

   At 11:39 AM 5/23/03, Mukta Telang wrote:
   
   >Hi,
   >I am dealing with a database with many-to-many relation and have a php
   >script that joins these tables and displays the output.
   >But the problem is that after entering some 50 records the output is
   >very slow!
   >I can count till 50 before the script produces the output!
   >The complete description of the problem is written below..
   >Thanks in advance,
   >Mukta
   >
   >
   >
   >
   >description in detail:
   >
   >
   >database part:
   >
   >Three tables: paper,author and author_paper
   >
   >"A paper has many authors and an author has many papers."
   >In this way there is many to many relation.
   >
   >The attributes of paper are: paper_id (primary key),title,journal,year
   >etc
   >
   >The attributes of author are: author_id(primary key) and name
   >
   >The attributes of author_paper are: author_id ,paper_id and level
   >( author with level=1 is the main author of the paper )
   >
   >
   >
   >php part:
   >
   >For an author:
   >1. get author_id from author table
   >2. select all paper_ids from author_paper table
   >3. For each of the above selected paper_ids:
   >         a. print all the attributes of paper
   >         b. select all authors of the paper and print their names in the
   >order of their "level"
   
   
   Are you doing this with one select, or four selects, or something in 
   between?  IAC, this doesn't sond like a php issue, but rather an issue with 
   how you build your select statement.  If your SELECT statment is generated 
   dynamically, then print it out when the script runs.  Then copy/paste the 
   final SELECT statement, add "EXPLAIN" to the beginning of it and run it via 
   psql.
   
   Now you have enough info to post a question to pgsql-sql@postgresql.org - 
   include the following:
   - schema of various table involved (like you did above).
   - copy of SELECT statement(s)
   - output of EXPLAIN for the SELECT statement that is taking too long.
   
   Frank 
   
   
   

4. Re: faster output from php and postgres

   Lonnie VanZandt <lonniev@predictableresponse.com> — 2003-05-23T17:26:08Z

   The entire report should/could be a single SQL query with multi-table
   joins and order by specifications - but I believe Mukta is doing
   individual table queries and looping over result sets in the PHP code.
   Seeing actual code will help...
   
   -----Original Message-----
   From: pgsql-php-owner@postgresql.org
   [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
   Sent: Friday, May 23, 2003 10:04 AM
   To: Mukta Telang
   Cc: pgsql-php@postgresql.org
   Subject: Re: [PHP] faster output from php and postgres
   
   
   It's probably just a slow query.
   
   Could you print out your query in PHP to the web browser, then cut and 
   paste it into the psql monitor to get an explain anaylze output?
   
   i.e.
   psql dbname
   #> explain analyze select .... rest of query goes here;
   
   And give us the output of that?  Thanks.
   
   On Fri, 23 May 2003, Mukta Telang wrote:
   
   > Hi,
   > I am dealing with a database with many-to-many relation and have a php
   
   > script that joins these tables and displays the output. But the 
   > problem is that after entering some 50 records the output is very 
   > slow! I can count till 50 before the script produces the output!
   > The complete description of the problem is written below..
   > Thanks in advance,
   > Mukta
   > 
   > 
   > 
   > 
   > description in detail:
   > 
   > 
   > database part:
   > 
   > Three tables: paper,author and author_paper
   > 
   > "A paper has many authors and an author has many papers."
   > In this way there is many to many relation.
   > 
   > The attributes of paper are: paper_id (primary key),title,journal,year
   
   > etc
   > 
   > The attributes of author are: author_id(primary key) and name
   > 
   > The attributes of author_paper are: author_id ,paper_id and level ( 
   > author with level=1 is the main author of the paper )
   > 
   > 
   > 
   > php part:
   > 
   > For an author:
   > 1. get author_id from author table
   > 2. select all paper_ids from author_paper table
   > 3. For each of the above selected paper_ids:
   >         a. print all the attributes of paper
   >         b. select all authors of the paper and print their names in 
   > the order of their "level"
   > 
   > ---------------------------(end of 
   > broadcast)---------------------------
   > TIP 1: subscribe and unsubscribe commands go to
   majordomo@postgresql.org
   > 
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 2: you can get off all lists at once with the unregister command
       (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   
   
   
   

5. Re: faster output from php and postgres

   scott.marlowe <scott.marlowe@ihs.com> — 2003-05-23T17:53:56Z

   Dear god, I saw the word join in his explanation and didn't even read much 
   after that.  I thought he meant join as in join.  
   
   Even so, I bet he's still doing something else wrong, I can loop across 50 
   queries in a blink of an eye.  Maybe he's disconnecting / reconnecting on 
   every access, or worse, just connecting over and over.
   
   I've seen some crazy code in PHP when folks are just beginning.  I think I 
   wrote a bit of it, but that was back when kids got suspended for saying 
   "I'm using PHP."  (<- true story, a kid had a run in with a teacher / 
   principle, and was nearly suspended for saying that.)
   
   On Fri, 23 May 2003, Lonnie VanZandt wrote:
   
   > The entire report should/could be a single SQL query with multi-table
   > joins and order by specifications - but I believe Mukta is doing
   > individual table queries and looping over result sets in the PHP code.
   > Seeing actual code will help...
   > 
   > -----Original Message-----
   > From: pgsql-php-owner@postgresql.org
   > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
   > Sent: Friday, May 23, 2003 10:04 AM
   > To: Mukta Telang
   > Cc: pgsql-php@postgresql.org
   > Subject: Re: [PHP] faster output from php and postgres
   > 
   > 
   > It's probably just a slow query.
   > 
   > Could you print out your query in PHP to the web browser, then cut and 
   > paste it into the psql monitor to get an explain anaylze output?
   > 
   > i.e.
   > psql dbname
   > #> explain analyze select .... rest of query goes here;
   > 
   > And give us the output of that?  Thanks.
   > 
   > On Fri, 23 May 2003, Mukta Telang wrote:
   > 
   > > Hi,
   > > I am dealing with a database with many-to-many relation and have a php
   > 
   > > script that joins these tables and displays the output. But the 
   > > problem is that after entering some 50 records the output is very 
   > > slow! I can count till 50 before the script produces the output!
   > > The complete description of the problem is written below..
   > > Thanks in advance,
   > > Mukta
   > > 
   > > 
   > > 
   > > 
   > > description in detail:
   > > 
   > > 
   > > database part:
   > > 
   > > Three tables: paper,author and author_paper
   > > 
   > > "A paper has many authors and an author has many papers."
   > > In this way there is many to many relation.
   > > 
   > > The attributes of paper are: paper_id (primary key),title,journal,year
   > 
   > > etc
   > > 
   > > The attributes of author are: author_id(primary key) and name
   > > 
   > > The attributes of author_paper are: author_id ,paper_id and level ( 
   > > author with level=1 is the main author of the paper )
   > > 
   > > 
   > > 
   > > php part:
   > > 
   > > For an author:
   > > 1. get author_id from author table
   > > 2. select all paper_ids from author_paper table
   > > 3. For each of the above selected paper_ids:
   > >         a. print all the attributes of paper
   > >         b. select all authors of the paper and print their names in 
   > > the order of their "level"
   > > 
   > > ---------------------------(end of 
   > > broadcast)---------------------------
   > > TIP 1: subscribe and unsubscribe commands go to
   > majordomo@postgresql.org
   > > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 2: you can get off all lists at once with the unregister command
   >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 4: Don't 'kill -9' the postmaster
   > 
   
   
   

6. Re: faster output from php and postgres

   Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-24T15:50:05Z

   I'm glad this came up, because I have the same type of problem.  Except,
   I don't see how a join can work... of course, I'm not really schooled in
   this stuff.
   
   I also have three tables: author, publication, and pub_auth.
   
   There are multiple authors for some publications, so it is necessary to
   check each publication selected for ALL authors.  I'm doing this with a
   foreach loop on the result of each publication key returned.
   
   How would a join make this easier?
   
   I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
   not sure what to search for...
   
   On Fri, 23 May 2003, scott.marlowe wrote:
   
   > Dear god, I saw the word join in his explanation and didn't even read much 
   > after that.  I thought he meant join as in join.  
   > 
   > Even so, I bet he's still doing something else wrong, I can loop across 50 
   > queries in a blink of an eye.  Maybe he's disconnecting / reconnecting on 
   > every access, or worse, just connecting over and over.
   > 
   > I've seen some crazy code in PHP when folks are just beginning.  I think I 
   > wrote a bit of it, but that was back when kids got suspended for saying 
   > "I'm using PHP."  (<- true story, a kid had a run in with a teacher / 
   > principle, and was nearly suspended for saying that.)
   > 
   > On Fri, 23 May 2003, Lonnie VanZandt wrote:
   > 
   > > The entire report should/could be a single SQL query with multi-table
   > > joins and order by specifications - but I believe Mukta is doing
   > > individual table queries and looping over result sets in the PHP code.
   > > Seeing actual code will help...
   > > 
   > > -----Original Message-----
   > > From: pgsql-php-owner@postgresql.org
   > > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
   > > Sent: Friday, May 23, 2003 10:04 AM
   > > To: Mukta Telang
   > > Cc: pgsql-php@postgresql.org
   > > Subject: Re: [PHP] faster output from php and postgres
   > > 
   > > 
   > > It's probably just a slow query.
   > > 
   > > Could you print out your query in PHP to the web browser, then cut and 
   > > paste it into the psql monitor to get an explain anaylze output?
   > > 
   > > i.e.
   > > psql dbname
   > > #> explain analyze select .... rest of query goes here;
   > > 
   > > And give us the output of that?  Thanks.
   > > 
   > > On Fri, 23 May 2003, Mukta Telang wrote:
   > > 
   > > > Hi,
   > > > I am dealing with a database with many-to-many relation and have a php
   > > 
   > > > script that joins these tables and displays the output. But the 
   > > > problem is that after entering some 50 records the output is very 
   > > > slow! I can count till 50 before the script produces the output!
   > > > The complete description of the problem is written below..
   > > > Thanks in advance,
   > > > Mukta
   > > > 
   > > > 
   > > > 
   > > > 
   > > > description in detail:
   > > > 
   > > > 
   > > > database part:
   > > > 
   > > > Three tables: paper,author and author_paper
   > > > 
   > > > "A paper has many authors and an author has many papers."
   > > > In this way there is many to many relation.
   > > > 
   > > > The attributes of paper are: paper_id (primary key),title,journal,year
   > > 
   > > > etc
   > > > 
   > > > The attributes of author are: author_id(primary key) and name
   > > > 
   > > > The attributes of author_paper are: author_id ,paper_id and level ( 
   > > > author with level=1 is the main author of the paper )
   > > > 
   > > > 
   > > > 
   > > > php part:
   > > > 
   > > > For an author:
   > > > 1. get author_id from author table
   > > > 2. select all paper_ids from author_paper table
   > > > 3. For each of the above selected paper_ids:
   > > >         a. print all the attributes of paper
   > > >         b. select all authors of the paper and print their names in 
   > > > the order of their "level"
   > > > 
   > > > ---------------------------(end of 
   > > > broadcast)---------------------------
   > > > TIP 1: subscribe and unsubscribe commands go to
   > > majordomo@postgresql.org
   > > > 
   > > 
   > > 
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 2: you can get off all lists at once with the unregister command
   > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > > 
   > > 
   > > 
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 4: Don't 'kill -9' the postmaster
   > > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   > 
   > http://archives.postgresql.org
   > 
   
   -Chadwick
   
   
   

7. Re: faster output from php and postgres

   Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-24T17:02:29Z

   As a person with the same problem, I would love to know how to do this.
   The concept escapes me, but maybe these lists could help with novice
   sqlers.
   
   If there are multiple authors per publication (my case), and each author
   needs to be selected and displayed, how can that be done via a join?
   
   One would need to query for all authors with each publication.
   
   I'm cross-posting this, because it seems relevant on both ends.
   
   Thanks!
   
   On Fri, 23 May 2003, Lonnie VanZandt wrote:
   
   > The entire report should/could be a single SQL query with multi-table
   > joins and order by specifications - but I believe Mukta is doing
   > individual table queries and looping over result sets in the PHP code.
   > Seeing actual code will help...
   > 
   > -----Original Message-----
   > From: pgsql-php-owner@postgresql.org
   > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
   > Sent: Friday, May 23, 2003 10:04 AM
   > To: Mukta Telang
   > Cc: pgsql-php@postgresql.org
   > Subject: Re: [PHP] faster output from php and postgres
   > 
   > 
   > It's probably just a slow query.
   > 
   > Could you print out your query in PHP to the web browser, then cut and 
   > paste it into the psql monitor to get an explain anaylze output?
   > 
   > i.e.
   > psql dbname
   > #> explain analyze select .... rest of query goes here;
   > 
   > And give us the output of that?  Thanks.
   > 
   > On Fri, 23 May 2003, Mukta Telang wrote:
   > 
   > > Hi,
   > > I am dealing with a database with many-to-many relation and have a php
   > 
   > > script that joins these tables and displays the output. But the 
   > > problem is that after entering some 50 records the output is very 
   > > slow! I can count till 50 before the script produces the output!
   > > The complete description of the problem is written below..
   > > Thanks in advance,
   > > Mukta
   > > 
   > > 
   > > 
   > > 
   > > description in detail:
   > > 
   > > 
   > > database part:
   > > 
   > > Three tables: paper,author and author_paper
   > > 
   > > "A paper has many authors and an author has many papers."
   > > In this way there is many to many relation.
   > > 
   > > The attributes of paper are: paper_id (primary key),title,journal,year
   > 
   > > etc
   > > 
   > > The attributes of author are: author_id(primary key) and name
   > > 
   > > The attributes of author_paper are: author_id ,paper_id and level ( 
   > > author with level=1 is the main author of the paper )
   > > 
   > > 
   > > 
   > > php part:
   > > 
   > > For an author:
   > > 1. get author_id from author table
   > > 2. select all paper_ids from author_paper table
   > > 3. For each of the above selected paper_ids:
   > >         a. print all the attributes of paper
   > >         b. select all authors of the paper and print their names in 
   > > the order of their "level"
   > > 
   > > ---------------------------(end of 
   > > broadcast)---------------------------
   > > TIP 1: subscribe and unsubscribe commands go to
   > majordomo@postgresql.org
   > > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 2: you can get off all lists at once with the unregister command
   >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > 
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 4: Don't 'kill -9' the postmaster
   > 
   
   -Chadwick
   
   
   

8. Re: faster output from php and postgres

   Bruno Wolff III <bruno@wolff.to> — 2003-05-24T22:00:09Z

   On Sat, May 24, 2003 at 13:02:29 -0400,
     Chadwick Rolfs <cmr@shell.gis.net> wrote:
   > As a person with the same problem, I would love to know how to do this.
   > The concept escapes me, but maybe these lists could help with novice
   > sqlers.
   > 
   > If there are multiple authors per publication (my case), and each author
   > needs to be selected and displayed, how can that be done via a join?
   > 
   > One would need to query for all authors with each publication.
   
   You would do something like:
   select pub.pubname, auth.authname
     from pub, auth, pub_auth
     where
       pub.pubid = pub_auth.pubid and
       auth.authid = pub_auth.authid
     order by pub.pubname, auth.authname;
   
   

9. Re: faster output from php and postgres

   Frank Bax <fbax@sympatico.ca> — 2003-05-25T02:01:30Z

   Finding previous examples of complex joins in archives is not likely an 
   easy thing to find.
   
   pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
   
   Change [database] to the name of your database - this command will dump out 
   schema relative to your request.  Post the results to this list.  Then ask 
   us the question "how do I write a SELECT that produces...[ you finish this 
   sentence]".  Question probably more appropriate to the list you mentioned, 
   but I expect there are people here who are just as capable of answering the 
   question.  I've even seen examples where the process goes through several 
   emails before SQL produces desired results exactly.
   
    >How would a join make this easier?
   
   I have always found that one properly constructed complex query is always 
   "cheaper" in runtime than numerous queries inside a foreach loop.  Your 
   final query will likely include joining a table to itself (this can 
   sometimes be a difficult concept to grasp).
   
   Frank
   
   
   At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
   
   
   >I'm glad this came up, because I have the same type of problem.  Except,
   >I don't see how a join can work... of course, I'm not really schooled in
   >this stuff.
   >
   >I also have three tables: author, publication, and pub_auth.
   >
   >There are multiple authors for some publications, so it is necessary to
   >check each publication selected for ALL authors.  I'm doing this with a
   >foreach loop on the result of each publication key returned.
   >
   >How would a join make this easier?
   >
   >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
   >not sure what to search for...
   >
   >On Fri, 23 May 2003, scott.marlowe wrote:
   >
   > > Dear god, I saw the word join in his explanation and didn't even read much
   > > after that.  I thought he meant join as in join.
   > >
   > > Even so, I bet he's still doing something else wrong, I can loop across 50
   > > queries in a blink of an eye.  Maybe he's disconnecting / reconnecting on
   > > every access, or worse, just connecting over and over.
   > >
   > > I've seen some crazy code in PHP when folks are just beginning.  I think I
   > > wrote a bit of it, but that was back when kids got suspended for saying
   > > "I'm using PHP."  (<- true story, a kid had a run in with a teacher /
   > > principle, and was nearly suspended for saying that.)
   > >
   > > On Fri, 23 May 2003, Lonnie VanZandt wrote:
   > >
   > > > The entire report should/could be a single SQL query with multi-table
   > > > joins and order by specifications - but I believe Mukta is doing
   > > > individual table queries and looping over result sets in the PHP code.
   > > > Seeing actual code will help...
   > > >
   > > > -----Original Message-----
   > > > From: pgsql-php-owner@postgresql.org
   > > > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
   > > > Sent: Friday, May 23, 2003 10:04 AM
   > > > To: Mukta Telang
   > > > Cc: pgsql-php@postgresql.org
   > > > Subject: Re: [PHP] faster output from php and postgres
   > > >
   > > >
   > > > It's probably just a slow query.
   > > >
   > > > Could you print out your query in PHP to the web browser, then cut and
   > > > paste it into the psql monitor to get an explain anaylze output?
   > > >
   > > > i.e.
   > > > psql dbname
   > > > #> explain analyze select .... rest of query goes here;
   > > >
   > > > And give us the output of that?  Thanks.
   > > >
   > > > On Fri, 23 May 2003, Mukta Telang wrote:
   > > >
   > > > > Hi,
   > > > > I am dealing with a database with many-to-many relation and have a php
   > > >
   > > > > script that joins these tables and displays the output. But the
   > > > > problem is that after entering some 50 records the output is very
   > > > > slow! I can count till 50 before the script produces the output!
   > > > > The complete description of the problem is written below..
   > > > > Thanks in advance,
   > > > > Mukta
   > > > >
   > > > >
   > > > >
   > > > >
   > > > > description in detail:
   > > > >
   > > > >
   > > > > database part:
   > > > >
   > > > > Three tables: paper,author and author_paper
   > > > >
   > > > > "A paper has many authors and an author has many papers."
   > > > > In this way there is many to many relation.
   > > > >
   > > > > The attributes of paper are: paper_id (primary key),title,journal,year
   > > >
   > > > > etc
   > > > >
   > > > > The attributes of author are: author_id(primary key) and name
   > > > >
   > > > > The attributes of author_paper are: author_id ,paper_id and level (
   > > > > author with level=1 is the main author of the paper )
   > > > >
   > > > >
   > > > >
   > > > > php part:
   > > > >
   > > > > For an author:
   > > > > 1. get author_id from author table
   > > > > 2. select all paper_ids from author_paper table
   > > > > 3. For each of the above selected paper_ids:
   > > > >         a. print all the attributes of paper
   > > > >         b. select all authors of the paper and print their names in
   > > > > the order of their "level"
   > > > >
   > > > > ---------------------------(end of
   > > > > broadcast)---------------------------
   > > > > TIP 1: subscribe and unsubscribe commands go to
   > > > majordomo@postgresql.org
   > > > >
   > > >
   > > >
   > > > ---------------------------(end of broadcast)---------------------------
   > > > TIP 2: you can get off all lists at once with the unregister command
   > > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > > >
   > > >
   > > >
   > > > ---------------------------(end of broadcast)---------------------------
   > > > TIP 4: Don't 'kill -9' the postmaster
   > > >
   > >
   > >
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 6: Have you searched our list archives?
   > >
   > > http://archives.postgresql.org
   > >
   >
   >-Chadwick
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 5: Have you checked our extensive FAQ?
   >
   >http://www.postgresql.org/docs/faqs/FAQ.html
   
   
   

10. Re: faster output from php and postgres

    Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-27T16:34:47Z

    So, I have the same problem, but I need all authors for each publication
    to show up in it's own column.  I tried the full join query from a
    suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    of ALL authors at once.
    
    I'll do some RTFMing of the joins.. and post any results I get
    
    BUT, right now, looping over each publication with php isn't taking that
    long.  I would like to know how to make this query, though!
    
    Please let me know how to get a result like:
    ____________________________________________________
    |All Authors|Title|Source|Year|Type|Length|Keywords|
    ----------------------------------------------------
    
    If there is a way on the SQL side to do this, that is ;>
    
    Here we go:
    
    CREATE TABLE "author" (
            "auth_id" integer DEFAULT
    nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
            "first" text,
            "last" text,
            "auth_last_updated" timestamp with time zone,
            Constraint "author_temp_pkey" Primary Key ("auth_id")
    );
    
    
    CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
    
    
    CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
    (auth_last_updated);
    
    
    
    CREATE TABLE "publication" (
            "copyis" text,
            "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
    NOT NULL,
            "title" text,
            "source" text,
            "year" text,
            "month" text,
            "length" text,
            "type" text,
            "keywords" text,
            "copyright" text,
            "abstract" text,
            "pdformat" text,
            "pub_last_updated" timestamp with time zone
    );
    
    
    
    CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
    (pub_id);
    
    
    CREATE INDEX keywords_publication_key ON publication USING btree
    (keywords);
    
    
    CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
    btree (pub_last_updated);
    
    
    CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
    (pub_id);
    
    
    
    CREATE TABLE "pub_auth" (
            "pub_auth_id" integer DEFAULT
    nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
            "pub_id" integer,
            "auth_id" integer,
            Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
    );
    
    
    
    CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
    
    
    CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
    
    
    On Sat, 24 May 2003, Frank Bax wrote:
    
    > Finding previous examples of complex joins in archives is not likely an 
    > easy thing to find.
    > 
    > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
    > 
    > Change [database] to the name of your database - this command will dump out 
    > schema relative to your request.  Post the results to this list.  Then ask 
    > us the question "how do I write a SELECT that produces...[ you finish this 
    > sentence]".  Question probably more appropriate to the list you mentioned, 
    > but I expect there are people here who are just as capable of answering the 
    > question.  I've even seen examples where the process goes through several 
    > emails before SQL produces desired results exactly.
    > 
    >  >How would a join make this easier?
    > 
    > I have always found that one properly constructed complex query is always 
    > "cheaper" in runtime than numerous queries inside a foreach loop.  Your 
    > final query will likely include joining a table to itself (this can 
    > sometimes be a difficult concept to grasp).
    > 
    > Frank
    > 
    > 
    > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
    > 
    > 
    > >I'm glad this came up, because I have the same type of problem.  Except,
    > >I don't see how a join can work... of course, I'm not really schooled in
    > >this stuff.
    > >
    > >I also have three tables: author, publication, and pub_auth.
    > >
    > >There are multiple authors for some publications, so it is necessary to
    > >check each publication selected for ALL authors.  I'm doing this with a
    > >foreach loop on the result of each publication key returned.
    > >
    > >How would a join make this easier?
    > >
    > >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
    > >not sure what to search for...
    > >
    
    
    -Chadwick
    
    
    
    

11. Re: [SQL] faster output from php and postgres

    Richard Huxton <dev@archonet.com> — 2003-05-27T18:19:05Z

    On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
    > So, I have the same problem, but I need all authors for each publication
    > to show up in it's own column.  I tried the full join query from a
    > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > of ALL authors at once.
    >
    > I'll do some RTFMing of the joins.. and post any results I get
    >
    > BUT, right now, looping over each publication with php isn't taking that
    > long.  I would like to know how to make this query, though!
    >
    > Please let me know how to get a result like:
    > ____________________________________________________
    >
    > |All Authors|Title|Source|Year|Type|Length|Keywords|
    
    Well, if you search the archives for terms "text", "concat", "aggregate" you 
    should come up with one solution. This involves writing your own aggregate 
    function, like SUM() but for text. Don't worry, it's not difficult. The only 
    issue is that you won't be able to guarantee the order of authors in the 
    field.
    
    There might be something on this in my PostgreSQL Notes on 
    http://techdocs.postgresql.org/ too - can't remember for sure.
    
    -- 
      Richard Huxton
    
    

12. Re: [PHP] faster output from php and postgres

    Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-27T18:27:28Z

    On Tue, 27 May 2003, Richard Huxton wrote:
    
    > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
    > > So, I have the same problem, but I need all authors for each publication
    > > to show up in it's own column.  I tried the full join query from a
    > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > > of ALL authors at once.
    > >
    > > I'll do some RTFMing of the joins.. and post any results I get
    > >
    > > BUT, right now, looping over each publication with php isn't taking that
    > > long.  I would like to know how to make this query, though!
    > >
    > > Please let me know how to get a result like:
    > > ____________________________________________________
    > >
    > > |All Authors|Title|Source|Year|Type|Length|Keywords|
    > 
    > Well, if you search the archives for terms "text", "concat", "aggregate" you 
    > should come up with one solution. This involves writing your own aggregate 
    > function, like SUM() but for text. Don't worry, it's not difficult. The only 
    > issue is that you won't be able to guarantee the order of authors in the 
    > field.
    > 
    > There might be something on this in my PostgreSQL Notes on 
    > http://techdocs.postgresql.org/ too - can't remember for sure.
    > 
    > -- 
    >   Richard Huxton
    
    A function is definitely in need.  But I already have PHP doing something
    of this sort.  I realize that PostgreSQL would most likely do it faster.
    
    What I meant by the result output above was that there would be _many_
    results, but for each publication only ONE column for ALL authors in EACH
    row. (*whew*)  So perhaps a function, but not an aggregate function.
    
    I'll look into functions next, as it seems that there is no single SQL
    statement that can do this... I can't see how.
    
    -Chadwick
    
    
    

13. Re: faster output from php and postgres

    Rod Taylor <rbt@rbt.ca> — 2003-05-27T18:54:36Z

    On Tue, 2003-05-27 at 14:19, Richard Huxton wrote:
    > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
    > > So, I have the same problem, but I need all authors for each publication
    > > to show up in it's own column.  I tried the full join query from a
    > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > > of ALL authors at once.
    > >
    > > I'll do some RTFMing of the joins.. and post any results I get
    > >
    > > BUT, right now, looping over each publication with php isn't taking that
    > > long.  I would like to know how to make this query, though!
    > >
    > > Please let me know how to get a result like:
    > > ____________________________________________________
    > >
    > > |All Authors|Title|Source|Year|Type|Length|Keywords|
    > 
    > Well, if you search the archives for terms "text", "concat", "aggregate" you 
    > should come up with one solution. This involves writing your own aggregate 
    > function, like SUM() but for text. Don't worry, it's not difficult. The only 
    > issue is that you won't be able to guarantee the order of authors in the 
    > field.
    
    If order is required:
    
    SELECT custom_aggregate(author) as authors
      FROM (SELECT author FROM table ORDER BY author) AS tab;
    
    The above should give you authors in alphabetical order if
    custom_aggregate() was written to concatenate text.
    
    
    -- 
    Rod Taylor <rbt@rbt.ca>
    
    PGP Key: http://www.rbt.ca/rbtpub.asc
    

14. Re: [SQL] faster output from php and postgres

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-05-27T19:35:39Z

    KISS
    
    why not use PHP to concatenate the authors while pub_id is the same???
    If you insist on having each author in its own column, 
    put them at the end and concatenate with <TD>.
    
    jll
    
    
    Chadwick Rolfs wrote:
    > 
    > So, I have the same problem, but I need all authors for each publication
    > to show up in it's own column.  I tried the full join query from a
    > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > of ALL authors at once.
    > 
    > I'll do some RTFMing of the joins.. and post any results I get
    > 
    > BUT, right now, looping over each publication with php isn't taking that
    > long.  I would like to know how to make this query, though!
    > 
    > Please let me know how to get a result like:
    > ____________________________________________________
    > |All Authors|Title|Source|Year|Type|Length|Keywords|
    > ----------------------------------------------------
    > 
    > If there is a way on the SQL side to do this, that is ;>
    > 
    > Here we go:
    > 
    > CREATE TABLE "author" (
    >         "auth_id" integer DEFAULT
    > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
    >         "first" text,
    >         "last" text,
    >         "auth_last_updated" timestamp with time zone,
    >         Constraint "author_temp_pkey" Primary Key ("auth_id")
    > );
    > 
    > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
    > 
    > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
    > (auth_last_updated);
    > 
    > CREATE TABLE "publication" (
    >         "copyis" text,
    >         "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
    > NOT NULL,
    >         "title" text,
    >         "source" text,
    >         "year" text,
    >         "month" text,
    >         "length" text,
    >         "type" text,
    >         "keywords" text,
    >         "copyright" text,
    >         "abstract" text,
    >         "pdformat" text,
    >         "pub_last_updated" timestamp with time zone
    > );
    > 
    > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
    > (pub_id);
    > 
    > CREATE INDEX keywords_publication_key ON publication USING btree
    > (keywords);
    > 
    > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
    > btree (pub_last_updated);
    > 
    > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
    > (pub_id);
    > 
    > CREATE TABLE "pub_auth" (
    >         "pub_auth_id" integer DEFAULT
    > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
    >         "pub_id" integer,
    >         "auth_id" integer,
    >         Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
    > );
    > 
    > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
    > 
    > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
    > 
    > On Sat, 24 May 2003, Frank Bax wrote:
    > 
    > > Finding previous examples of complex joins in archives is not likely an
    > > easy thing to find.
    > >
    > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
    > >
    > > Change [database] to the name of your database - this command will dump out
    > > schema relative to your request.  Post the results to this list.  Then ask
    > > us the question "how do I write a SELECT that produces...[ you finish this
    > > sentence]".  Question probably more appropriate to the list you mentioned,
    > > but I expect there are people here who are just as capable of answering the
    > > question.  I've even seen examples where the process goes through several
    > > emails before SQL produces desired results exactly.
    > >
    > >  >How would a join make this easier?
    > >
    > > I have always found that one properly constructed complex query is always
    > > "cheaper" in runtime than numerous queries inside a foreach loop.  Your
    > > final query will likely include joining a table to itself (this can
    > > sometimes be a difficult concept to grasp).
    > >
    > > Frank
    > >
    > >
    > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
    > >
    > >
    > > >I'm glad this came up, because I have the same type of problem.  Except,
    > > >I don't see how a join can work... of course, I'm not really schooled in
    > > >this stuff.
    > > >
    > > >I also have three tables: author, publication, and pub_auth.
    > > >
    > > >There are multiple authors for some publications, so it is necessary to
    > > >check each publication selected for ALL authors.  I'm doing this with a
    > > >foreach loop on the result of each publication key returned.
    > > >
    > > >How would a join make this easier?
    > > >
    > > >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
    > > >not sure what to search for...
    > > >
    > 
    > -Chadwick
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 2: you can get off all lists at once with the unregister command
    >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
    
    

15. Re: [SQL] faster output from php and postgres

    Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-27T20:29:16Z

    Yes, this may be better than foreach()ing through each publication
    returned.
    
    But a plpgsql function may suit these needs much better.
    
    On Tue, 27 May 2003, Jean-Luc Lachance wrote:
    
    > KISS
    > 
    > why not use PHP to concatenate the authors while pub_id is the same???
    > If you insist on having each author in its own column, 
    > put them at the end and concatenate with <TD>.
    > 
    > jll
    > 
    > 
    > Chadwick Rolfs wrote:
    > > 
    > > So, I have the same problem, but I need all authors for each publication
    > > to show up in it's own column.  I tried the full join query from a
    > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > > of ALL authors at once.
    > > 
    > > I'll do some RTFMing of the joins.. and post any results I get
    > > 
    > > BUT, right now, looping over each publication with php isn't taking that
    > > long.  I would like to know how to make this query, though!
    > > 
    > > Please let me know how to get a result like:
    > > ____________________________________________________
    > > |All Authors|Title|Source|Year|Type|Length|Keywords|
    > > ----------------------------------------------------
    > > 
    > > If there is a way on the SQL side to do this, that is ;>
    > > 
    > > Here we go:
    > > 
    > > CREATE TABLE "author" (
    > >         "auth_id" integer DEFAULT
    > > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
    > >         "first" text,
    > >         "last" text,
    > >         "auth_last_updated" timestamp with time zone,
    > >         Constraint "author_temp_pkey" Primary Key ("auth_id")
    > > );
    > > 
    > > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
    > > 
    > > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
    > > (auth_last_updated);
    > > 
    > > CREATE TABLE "publication" (
    > >         "copyis" text,
    > >         "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
    > > NOT NULL,
    > >         "title" text,
    > >         "source" text,
    > >         "year" text,
    > >         "month" text,
    > >         "length" text,
    > >         "type" text,
    > >         "keywords" text,
    > >         "copyright" text,
    > >         "abstract" text,
    > >         "pdformat" text,
    > >         "pub_last_updated" timestamp with time zone
    > > );
    > > 
    > > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
    > > (pub_id);
    > > 
    > > CREATE INDEX keywords_publication_key ON publication USING btree
    > > (keywords);
    > > 
    > > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
    > > btree (pub_last_updated);
    > > 
    > > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
    > > (pub_id);
    > > 
    > > CREATE TABLE "pub_auth" (
    > >         "pub_auth_id" integer DEFAULT
    > > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
    > >         "pub_id" integer,
    > >         "auth_id" integer,
    > >         Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
    > > );
    > > 
    > > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
    > > 
    > > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
    > > 
    > > On Sat, 24 May 2003, Frank Bax wrote:
    > > 
    > > > Finding previous examples of complex joins in archives is not likely an
    > > > easy thing to find.
    > > >
    > > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
    > > >
    > > > Change [database] to the name of your database - this command will dump out
    > > > schema relative to your request.  Post the results to this list.  Then ask
    > > > us the question "how do I write a SELECT that produces...[ you finish this
    > > > sentence]".  Question probably more appropriate to the list you mentioned,
    > > > but I expect there are people here who are just as capable of answering the
    > > > question.  I've even seen examples where the process goes through several
    > > > emails before SQL produces desired results exactly.
    > > >
    > > >  >How would a join make this easier?
    > > >
    > > > I have always found that one properly constructed complex query is always
    > > > "cheaper" in runtime than numerous queries inside a foreach loop.  Your
    > > > final query will likely include joining a table to itself (this can
    > > > sometimes be a difficult concept to grasp).
    > > >
    > > > Frank
    > > >
    > > >
    > > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
    > > >
    > > >
    > > > >I'm glad this came up, because I have the same type of problem.  Except,
    > > > >I don't see how a join can work... of course, I'm not really schooled in
    > > > >this stuff.
    > > > >
    > > > >I also have three tables: author, publication, and pub_auth.
    > > > >
    > > > >There are multiple authors for some publications, so it is necessary to
    > > > >check each publication selected for ALL authors.  I'm doing this with a
    > > > >foreach loop on the result of each publication key returned.
    > > > >
    > > > >How would a join make this easier?
    > > > >
    > > > >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
    > > > >not sure what to search for...
    > > > >
    > > 
    > > -Chadwick
    > > 
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 2: you can get off all lists at once with the unregister command
    > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
    > 
    
    -Chadwick
    
    
    

16. Re: [SQL] faster output from php and postgres (one resolution)

    Chadwick Rolfs <cmr@shell.gis.net> — 2003-05-29T19:53:14Z

    My solution to the problem stated below was to learn plpgsql, and write a
    function that loops through the returned records, concatenating a string
    together, and returning that string as the Author field.
    This function was actually e-mailed to me by a collegue, and I did a bit
    of doctoring (it still needs it) to get it functional (pun perhaps
    intended :>):
    
    ----------------------------------------------------------------
    create function show_auth(integer) returns varchar as '
    declare
     a record;
     b alias for $1;
     s varchar;
     c varchar;
    begin
     s := '''';
     c := '''';
     for a in
       select author.last
       from pub_auth, author
       where pub_auth.pub_id = b
       and author.auth_id = pub_auth.auth_id
       order by author.last
     loop
       s := s || c|| a.last;
       c := '', '';
     end loop;
     return s;
    end;
    ' language 'plpgsql';
    ----------------------------------------------------------------
    
    Although there were many ways to do this, as Jean-Luc points out below, I
    believe in the long run this one is best.
    This function will be expanded to actually handle the entire query that
    was starting with:
    
    select auth_id 
      from author 
      where 
        lower(last) like lower('%".addslashes($search_term)."%') 
      order by last, first
    
    -OR-
    
    select distinct pub_auth.pub_id, title, source, year, type, length,
    copyright, abstract, pdformat, keywords 
      from publication, pub_auth 
      where 
        keywords like '%".addslashes($search_term)."%' 
        and
        publication.pub_id = pub_auth.pub_id 
      order by year desc, title
    
    In other words, two different functions.  I guess.
    
    Hopefully this helps anyone else in the future!
    
    On Tue, 27 May 2003, Jean-Luc Lachance wrote:
    
    > KISS
    > 
    > why not use PHP to concatenate the authors while pub_id is the same???
    > If you insist on having each author in its own column, 
    > put them at the end and concatenate with <TD>.
    > 
    > jll
    > 
    > 
    > Chadwick Rolfs wrote:
    > > 
    > > So, I have the same problem, but I need all authors for each publication
    > > to show up in it's own column.  I tried the full join query from a
    > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
    > > of ALL authors at once.
    > > 
    > > I'll do some RTFMing of the joins.. and post any results I get
    > > 
    > > BUT, right now, looping over each publication with php isn't taking that
    > > long.  I would like to know how to make this query, though!
    > > 
    > > Please let me know how to get a result like:
    > > ____________________________________________________
    > > |All Authors|Title|Source|Year|Type|Length|Keywords|
    > > ----------------------------------------------------
    > > 
    > > If there is a way on the SQL side to do this, that is ;>
    > > 
    > > Here we go:
    > > 
    > > CREATE TABLE "author" (
    > >         "auth_id" integer DEFAULT
    > > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
    > >         "first" text,
    > >         "last" text,
    > >         "auth_last_updated" timestamp with time zone,
    > >         Constraint "author_temp_pkey" Primary Key ("auth_id")
    > > );
    > > 
    > > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
    > > 
    > > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
    > > (auth_last_updated);
    > > 
    > > CREATE TABLE "publication" (
    > >         "copyis" text,
    > >         "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
    > > NOT NULL,
    > >         "title" text,
    > >         "source" text,
    > >         "year" text,
    > >         "month" text,
    > >         "length" text,
    > >         "type" text,
    > >         "keywords" text,
    > >         "copyright" text,
    > >         "abstract" text,
    > >         "pdformat" text,
    > >         "pub_last_updated" timestamp with time zone
    > > );
    > > 
    > > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
    > > (pub_id);
    > > 
    > > CREATE INDEX keywords_publication_key ON publication USING btree
    > > (keywords);
    > > 
    > > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
    > > btree (pub_last_updated);
    > > 
    > > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
    > > (pub_id);
    > > 
    > > CREATE TABLE "pub_auth" (
    > >         "pub_auth_id" integer DEFAULT
    > > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
    > >         "pub_id" integer,
    > >         "auth_id" integer,
    > >         Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
    > > );
    > > 
    > > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
    > > 
    > > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
    > > 
    > > On Sat, 24 May 2003, Frank Bax wrote:
    > > 
    > > > Finding previous examples of complex joins in archives is not likely an
    > > > easy thing to find.
    > > >
    > > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
    > > >
    > > > Change [database] to the name of your database - this command will dump out
    > > > schema relative to your request.  Post the results to this list.  Then ask
    > > > us the question "how do I write a SELECT that produces...[ you finish this
    > > > sentence]".  Question probably more appropriate to the list you mentioned,
    > > > but I expect there are people here who are just as capable of answering the
    > > > question.  I've even seen examples where the process goes through several
    > > > emails before SQL produces desired results exactly.
    > > >
    > > >  >How would a join make this easier?
    > > >
    > > > I have always found that one properly constructed complex query is always
    > > > "cheaper" in runtime than numerous queries inside a foreach loop.  Your
    > > > final query will likely include joining a table to itself (this can
    > > > sometimes be a difficult concept to grasp).
    > > >
    > > > Frank
    > > >
    > > >
    > > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
    > > >
    > > >
    > > > >I'm glad this came up, because I have the same type of problem.  Except,
    > > > >I don't see how a join can work... of course, I'm not really schooled in
    > > > >this stuff.
    > > > >
    > > > >I also have three tables: author, publication, and pub_auth.
    > > > >
    > > > >There are multiple authors for some publications, so it is necessary to
    > > > >check each publication selected for ALL authors.  I'm doing this with a
    > > > >foreach loop on the result of each publication key returned.
    > > > >
    > > > >How would a join make this easier?
    > > > >
    > > > >I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
    > > > >not sure what to search for...
    > > > >
    > > 
    > > -Chadwick
    > > 
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 2: you can get off all lists at once with the unregister command
    > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
    > 
    
    -Chadwick