Re: Functions on tables

"Brendan Jurd" <direvus(at)gmail(dot)com> writes:
> That works fine, but wouldn't it be far more elegant if you could do
> this instead:

> CREATE TABLE person (
> id SERIAL PRIMARY KEY,
> firstname TEXT NOT NULL,
> lastname TEXT NOT NULL,
> FUNCTION name() RETURNS text AS $$ SELECT firstname || ' ' ||
> lastname; $$ LANGUAGE SQL IMMUTABLE
> );

90% of the value this would have is already available with views,
I think, without going outside bog-standard SQL:

create view ...
firstname || ' ' || lastname as name,
...

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:

regression=# create function name(person) returns text as $$
regression$# select $1.firstname || ' ' || $1.lastname
regression$# $$ language sql immutable;
CREATE FUNCTION
regression=# select person.name from person;
name
----------
joe blow
(1 row)

> Now the function name() belongs to the "person" table: it is, in
> effect, a method of the "person" class. Which means we can do this:
> SELECT id, name() FROM person ORDER BY name();

[ 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.

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.

regards, tom lane