On 12/17/06, Tom Lane <tgl@xxxxxxxxxxxxx> 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.
