Re: Functions on tables

On Mon, Dec 18, 2006 at 12:15:34AM +1100, Brendan Jurd wrote:
> 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)
> );

Illustra had the equivalent of columns defined as expressions.
I don't remember the syntax, but it was simpler than "GENERATED".
But better go with the spec.

Late on the thread,

--elein

>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>