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