postgresql - Efficiently select the most specific result from a table -

i have table follows:

create table t_table (     f_userid       bigint not null    ,f_groupaid     bigint    ,f_groupbid     bigint    ,f_groupcid     bigint    ,f_itemid       bigint    ,f_value        text ); 

the groups orthogonal, no hierarchy can implied beyond fact every entry in table have user id. there no uniqueness in of columns.

so example simple setup might be:

insert t_table values (1, null, null, null, null, 'value user 1'); insert t_table values (1,    5,    2, null, null, 'value user 1 in groupa 5 groupb 2'); insert t_table values (1,    4, null,    1, null, 'value user 1  in groupa 5 , groupc 1'); insert t_table values (2, null, null, null, null, 'value user 2'); insert t_table values (2,    1, null, null, null, 'value user 2 in groupa 1'); insert t_table values (2,    1,    3,    4,    5, 'value item 5 user 2 in groupa 1 , groupb 3 , groupc 4'); 

for given set of user/groupa/groupb/groupc/item want able obtain specific item in table applies. if of given set null can match relevant columns in table contain null. example:

// exact match select mostspecific(1, null, null, null, null) => "value user 1"  // match second entry because groupc , item not specified in table , other items matched select mostspecific(1, 5, 2, 3, null) => "value user 1 in groupa 5 groupb 2" // not match second entry because groupa null in query , set in table select mostspecific(1, null, 2, 3, 4) => "value user 1" 

the obvious approach here stored procedure work through parameters , find out null , not, , call appropriate select statement. seems inefficient. there better way of doing this?

this should it, filter out non matching rows using where, rank remaining rows how match. if column doesn't match, whole bop expression result in null, filter out in outer query order match , limit result single best match.

create function mostspecific(bigint, bigint, bigint, bigint, bigint)  returns table(f_userid bigint, f_groupaid bigint, f_groupbid bigint, f_groupcid bigint, f_itemid bigint, f_value text) 'with cte (   select *,      case when f_groupaid null 0 when f_groupaid = $2 1 end +     case when f_groupbid null 0 when f_groupbid = $3 1 end +     case when f_groupcid null 0 when f_groupcid = $4 1 end +     case when f_itemid   null 0 when f_itemid   = $5 1 end bop    t_table   f_userid = $1     , (f_groupaid null or f_groupaid = $2)     , (f_groupbid null or f_groupbid = $3)     , (f_groupcid null or f_groupcid = $4)     , (f_itemid null   or f_itemid   = $5) ) select f_userid, f_groupaid, f_groupbid, f_groupcid, f_itemid, f_value cte bop not null order bop desc limit 1' language sql // 

an sqlfiddle test with.