Re: Functions on tables

On 12/17/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> But having said all that, I think there are bits of SQL2003 that do some
> of what you're after. I don't think anyone has looked hard at what
> would be involved in merging those new SQL features with historical
> Postgres behaviors.

I've been looking into SQL2003, and there are indeed some features
there that correspond to what I want.

ISO/IEC 9075-2:2003 - Foundation (SQL/Foundation) talks about
"structured types" (like a user-defined composite type in Postgres,
and somewhat like a "struct" in C). It applies many OO concepts to
these structured types: inheritance, encapsulation, overloading. It
goes so far as to say that every structured type has an implied
constructor method, and for every attribute within the type, one
implied "observer" method and one implied "mutator" method.

The standard provides for adding user-defined methods to these types,
which have an implied first parameter "SELF", which is exactly the
sort of thing I am looking for. The standard doesn't specifically
mention doing this with tables, or row types. However, the conceptual
distinction between a user-defined composite type and a table is not
vast, and AIUI Postgres already implies a composite type for every
table, with each tuple in the table being an object of that composite
type.

Frankly I don't have much experience reading SQL standards, and the
language they use is a bit abstruse. But as far as I can tell, my
suggestion is quite nicely compliant with the behaviour the standard
recommends ... indeed the standard takes the OO idea much further than
I initially hoped to.

Alternatively, the standard also specifies "generated columns" within
a table, which would allow you to achieve a similar effect to my
person.name() method like so:

CREATE TABLE person (
id serial PRIMARY KEY,
firstname text NOT NULL,
lastname text NOT NULL,
name GENERATED ALWAYS AS (firstname || ' ' || lastname)
);

I think the generated column idea has some usefulness, but isn't quite
what I'm after. You can't use it with additional parameters (c.f. my
earlier birthday(int) example), and I like the idea of keeping
attributes and methods totally separate. A generated column is made
to appear like an attribute, and it is referenced like an attribute,
but in actual fact it is not an attribute, it is a derived value.