X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 58DC9474E4F for ; Thu, 8 May 2003 15:44:55 -0400 (EDT) Received: from taka.swcp.com (taka.swcp.com [198.59.115.12]) by postgresql.org (Postfix) with ESMTP id 9BAE44765A0 for ; Thu, 8 May 2003 15:44:52 -0400 (EDT) Received: from ARCplanning.com (pcp02742825pcs.sntafe01.nm.comcast.net [68.35.10.62]) by taka.swcp.com (8.12.9/8.12.9) with ESMTP id h48Jiopu015368; Thu, 8 May 2003 13:44:51 -0600 (MDT) Date: Thu, 8 May 2003 13:44:49 -0600 Subject: Re: does this require a stored procedure? Content-Type: text/plain; delsp=yes; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v552) Cc: pgsql-sql@postgresql.org To: chester c young From: Alex Rice In-Reply-To: <20030508192633.41386.qmail@web12701.mail.yahoo.com> Message-Id: <8766F03B-818D-11D7-A998-000393529642@ARCplanning.com> Content-Transfer-Encoding: 7bit X-Mailer: Apple Mail (2.552) X-Spam-Status: No, hits=-18.6 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,USER_AGENT_APPLEMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/181 X-Sequence-Number: 13346 On Thursday, May 8, 2003, at 01:26 PM, chester c young wrote: > do you mean something like: > > select t.url_id, t.url as title_url, t.sval as title_val, > c.url as content_url, c.sval as content_val > from > (select * from urlinfo where sname = 'title') t > join > (select * from urlinfo where sname = 'Content-type') c > using( url_id ); > Chester. Thanks, this helpful. However I think a stored procedure might be the way to go. See the url string exists only in the url table. Also the primary key is named url_id or rec_id depending which table, so the using(url_id) wouldn't work for that join. mnogosearch=> \d url Table "public.url" Column | Type | Modifiers -----------------+------------------ +------------------------------------------- ---- rec_id | integer | not null default nextval('next_url_id'::te xt) status | integer | not null default 0 docsize | integer | not null default 0 next_index_time | integer | not null last_mod_time | integer | referrer | integer | not null default 0 hops | integer | not null default 0 crc32 | integer | not null default 0 seed | integer | not null default 0 bad_since_time | integer | site_id | integer | server_id | integer | pop_rank | double precision | not null default 0 url | text | not null mnogosearch=> \d urlinfo Table "public.urlinfo" Column | Type | Modifiers --------+---------+----------- url_id | integer | not null sname | text | not null sval | text | not null Indexes: urlinfo_id btree (url_id) Alex Rice, Software Developer Architectural Research Consultants, Inc. alrice@ARCplanning.com alrice@swcp.com