Postgres Pro
Downloads
Home   >   mailing lists

Thread: does this require a stored procedure?

does this require a stored procedure?

From
Alex Rice
Date:
Is it possible to rewrite this SQL query so it returns one row having  
the columns title and contentType, instead of two rows with the sname  
column? I could write a stored procedure to lookup the title or  
content-type based on the rec_id, but I would rather do it in a single  
query if it's possible.

# SELECT rec_id, url, sname, sval FROM url, urlinfo
WHERE url.rec_id = 1821
AND url.rec_id = urlinfo.url_id
AND sname in('title','Content-Type');

rec_id |      url     |    sname     |        sval
-------+-------------- 
+---------------------------------------------------------   1821 | http://.../ | title        | Architectural Research

 
Consultants, Incorporated Contact Information   1821 | http://.../ | Content-Type | text/html
(2 rows)

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)

Thanks,

Alex Rice, Software Developer
Architectural Research Consultants, Inc.
alrice@ARCplanning.com
alrice@swcp.com

Re: does this require a stored procedure?

From
chester c young
Date:
--- Alex Rice <alrice@ARCplanning.com> wrote:
> Is it possible to rewrite this SQL query so it returns one row having
> the columns title and contentType, instead of two rows with the sname
> column? 
> 
> # SELECT rec_id, url, sname, sval FROM url, urlinfo
> WHERE url.rec_id = 1821
> AND url.rec_id = urlinfo.url_id
> AND sname in('title','Content-Type');
> 
> rec_id |      url     |    sname     |        sval
> -------+-------------- 
> +---------------------------------------------------------
>     1821 | http://.../ | title        | Architectural Research  
> Consultants, Incorporated Contact Information
>     1821 | http://.../ | Content-Type | text/html
> (2 rows)
> 
> 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)
> 
> Thanks,
> Alex Rice, Software Developer
> Architectural Research Consultants, Inc.
> alrice@ARCplanning.com
> alrice@swcp.com

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_valfrom    (select
*from urlinfo where sname = 'title') tjoin   (select * from urlinfo where sname = 'Content-type') cusing( url_id );
 


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

Re: does this require a stored procedure?

From
Alex Rice
Date:
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          |
notnull default 0 hops            | integer          | not null default 0 crc32           | integer          | not null
default0 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

Re: does this require a stored procedure?

From
Manfred Koizar
Date:
On Thu, 8 May 2003 12:50:02 -0600, Alex Rice <alrice@ARCplanning.com>
wrote:
>Is it possible to rewrite this SQL query so it returns one row having  
>the columns title and contentType, instead of two rows with the sname  
>column? [...]
>
># SELECT rec_id, url, sname, sval FROM url, urlinfo
>WHERE url.rec_id = 1821
>AND url.rec_id = urlinfo.url_id
>AND sname in('title','Content-Type');

SELECT u.rec_id, u.url, t.sval AS title, c.sval AS "contentType" FROM url u     INNER JOIN urlinfo t        ON
(u.rec_id= t.url_id AND t.sname = 'title')     INNER JOIN urlinfo c        ON (u.rec_id = c.url_id AND c.sname =
'Content-Type')WHEREurl.rec_id = 1821;
 

Use LEFT JOINs if it's not sure that there is always a 'title' and a
'Content-Type'.

ServusManfred