Tables as function arguments

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

Responses

Browse pgsql-general by date

  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