Re: Functions on tables

On 12/17/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 90% of the value this would have is already available with views,
> I think, without going outside bog-standard SQL:

Views also work fine, but one of the big advantages of having table
methods is that all the things your table can do are contained within
the table definition. Your table definition becomes like the API of a
class. When you use separate views and/or functions to provide
methods for a table, your schema becomes pretty scattered (I know mine
is). It can be non-trivial to find, say, all functions that relate to
a person tuple.

> Also, there's already a Berkeley-era syntax hack in PG that gets much of
> the rest: if x is of composite type, the notations x.y and y(x) are
> interchangeable. Thus:

I didn't know about that, and you're right, this actually delivers a
lot of the notational convenience that I'm looking for. But again,
you don't win the ability to look at your table definition and
immediately understand what methods the table exposes.

It looks like you can't use this hack to conveniently handle methods
that have arguments. For example, say you had a method to return a
person's birthday in a given year, defined as "birthday(person, int)
returns date". You wouldn't be able to write person.birthday(2007) in
a query and get the expected result.

> [ itch... ] That seems to risk breaking a whole lot of existing code by
> introducing name collisions --- the entire namespace of ordinary
> functions is at risk as soon as you have any of these per-table
> functions, if they can be called like that.
>

What if we used scope resolution? That is, Postgres first looks for
functions which are local to the table, and if it doesn't find a
match, then looks for functions in the normal namespace, perhaps
raising a notice to warn the user of the ambiguity.

Say there was a function name() in the public namespace. You can then
easily resolve the ambiguity by specifying either person.name() or
public.name().

Most front-end queries have more than one table in them, so in
practice I think you'll almost always be using table aliases and
identifying your methods explicitly anyway, e.g.:

SELECT p.name(), a.label() as address
FROM person p INNER JOIN address a ON p.postal_address = a.id;

... so I'm not passionately attached to the idea of being able to call
the method without prefixing the table name.