Skip site navigation (1) Skip section navigation (2)

Exact or less specific match ?

From: "NTPT" <ntpt(at)centrum(dot)cz>
To: "'Pgsql-General(at)Postgresql(dot)Org'" <pgsql-general(at)postgresql(dot)org>
Subject: Exact or less specific match ?
Date: 2004-10-27 15:33:03
Message-ID: 002101c4bc3a$3f8f7db0$74beebd5@wbp1 (view raw or flat)
Thread:
Lists: pgsql-general
Hi.

i  have table like this:

create table my_data (
 cond_1 int8,cond_2 varchar(),cond_3 .... cond_n whatrver ,data text)

This table represents a simple tree structure with known max level (n) . 
This table is filled with data, but branches have not a same depth.

Now I need to select from table

select data from my_data where cond_1=x AND cond_2='blah' And cond_3= ..... 
AND cond_n=whatewer

But, this tree have not a same depth in all his branches. So I need to 
select Exact match, and, if the exact match is not possible  (ie if there is 
not line that fit WHERE  condition ),

to select with WHERE  cond_1=x AND cond_2='blah' And cond_3= ..... AND 
cond_(n-1)=whatewer and so on until the 'data' is not empty or  top of the 
tree reached (ie if not match, find data from upper node of the tree).

I know, that similar effects can be reached with  COALESCE,


select coalesce ((select data from my_data where cond_1=x AND cond_2='blah' 
And cond_3= ..... AND cond_n=whatewer),(select data from my_data where 
cond_1=x AND cond_2='blah' And cond_3= ..... AND cond_(n-1)=whatewer) 
,...........,(select data from my_data where cond_1=x ))


but i think it is not ideal, because it needs to perform a N  subselects, 
what can eat a lot of machine time...



is there some other way to do exact or less specific match ?


Thank you. please execuse my bad english 


pgsql-general by date

Next:From: Jerry LeVanDate: 2004-10-27 15:40:17
Subject: Psycopg difficulty...
Previous:From: Tom LaneDate: 2004-10-27 15:29:39
Subject: Re: Resource temporarily unavailable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group