do i need a view or procedure?

Hello,

I am trying to get my head around a complex problem I've been tasked
with...

We have a set of servers spread throughout the country (100s to 1000s of

them) and I need to build a patch-distributing postgresql database. The

actual patches would be distributed via rpm's which we'd like to store
in the database (we pretty much have that part figured out). However,
there are various server configurations and only some patches are going
to be applicable to any one server. The decision process from an
out-in-the-field server's point of view is something like this:

- Connect to the central patch database
- What patches are available?
- Of these, which are intended for my OS?
- Of these, which are intended for the major version of the software
which I am running?
- Of these, which are not already installed here?

So that whittles down the number of eligible patches quite a bit. But
then there's more...

- Am I authorized to install patches designated for beta site or limited

release?
- Is this patch authorized for installation in my geographic region?
- Are there any patches that are targeted uniquely to me as a
site-specific patch?
- I'm running version 1.2 of the software. Is this patch numbered 1.0.7

intended specifically only for 1.0 installs or is it ok for 1.x installs

(we have both cases)?
etc...

As you can see, it becomes quite the tangled web.

The database has a few tables...a host table with an entry for each
field server, what their privileges are (beta site, limited release
site, etc...), what region they are in, what OS they are running, what
version of the software, etc...there's also a patches table, which
contain both the actual patch binary and all the various info about the
patch...what OS it's for...what version of the software it's for...if
it's for general use or for a specific host, etc... the idea is that the

client will be as dumb as possible. In other words, we hope it can be
as simple as "select * from patchview where serverid=xxx" or "select
availablepatches(xxx)" where xxx is the serverid.

So, my task is to figure out which host is asking for which patches are
available, and using that piece of information, build a result set that
includes only the patches that pass all the criteria for that host. I
see two approaches. The first is a view...drawbacks of the view
approach are that the view cannot tailor which rows it's presenting
based on a variable (i.e. a view can not present a different view of the

table depending on who connected can it?) I think I would need one view

for each server which quickly becomes unwieldy. Plus, I think the view
is going to need some crazy unions and where clauses to get the full set

and nothing but the set of eligible patches. The best we could come up
with is a view that has a 1-to-1 mapping of serverid to patchid. So if
you have 10 patches, and 10 servers, this view would have as many as 100

entries, if all 10 patches were applicable to all 10 servers. This gets

fairly large when you start looking at real world projections of how
many patches and servers we're going to have (something like maybe 100
patches x 10000 servers) and we're still not even sure if a view can
encompass all of the logic we need.

Another, more elegant possibility would be to use a procedure which
could build the result set piecewise (i.e. first get all the ones that
are marked specifically for this host, store them off to the side, then
go and get all the other ones that meet criteria B, add them to the
pile, then go get those that meet C and add them, etc....) and at the
end of the procedure, return a big result set to the client. However,
I don't see anything where the procedural languages can return a result
set to the client, i'm not even sure if they can always return even a
single row or not.

A third option is that neither of us tasked with this are database gurus

- we're mostly comfortable with them but it's not our bread and butter.
So maybe we're taking the wrong approach completely.

So, I'm just having a lot of trouble figuring out how to even approach
this, so any feedback at all would be greatly appreciated!

Thanks for reading this far :-)

Sincerely,
Fran