Обсуждение: Tables as function arguments

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

On Wednesday 24 March 2004 04:31, Jeremy Semeiks wrote:
> 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:
[snip]
> ... 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?

Not really - you might be able to use some sprintf() equivalent to make the
syntax prettier (or different, anyway) but you'll need to build a dynamic
query.

--
  Richard Huxton
  Archonet Ltd