From: | Jeremy Semeiks <jrs(at)denny(dot)farviolet(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Tables as function arguments |
Date: | 2004-03-24 04:31:43 |
Message-ID: | 20040324043143.GA13982@64.81.242.180 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I use psql interactively for data exploration, and often I want to do
the same types of generic set operations on data in many different
tables. For example, to calculate a similarity metric between 2
different tables I might type:
select i::float / u as similarity
from
(select count(*) as i
from (select * from foo intersect select * from bar) a) b,
(select count(*) as u
from (select * from foo union select * from bar) c) d;
Of course, I could make a function or view operating specifically on
the tables "foo" and "bar", but what I'd really like to do is abstract
this algorithm into a function with two relation-type arguments so I
can do something like:
select similarity(foo, bar);
Or:
select similarity(baz, qux);
Or even:
select similarity((select item from item_color where color = 'blue'),
(select item from item_color where color = 'black'));
As far as I can tell, functions can't include these types as
arguments. As a workaround, I can make a function that takes string
arguments and puts them into a query string as tables. In plpython,
this strategy looks like:
... plpy.execute("select * from "+args[0]+" union select * from "+args[1]) ...
But calling this function on the results of subselects turns into a
big syntactic mess. Is there a better way to do this kind of thing?
Thanks,
Jeremy
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph Shraibman | 2004-03-24 04:47:54 | Re: partial VACUUM FULL |
Previous Message | Joe Conway | 2004-03-24 04:29:33 | Re: partial VACUUM FULL |