Re: AW: functions returning records

On Wed, 27 Jun 2001, Karel Zak wrote:

> On Wed, Jun 27, 2001 at 10:56:43AM +0200, Reinoud van Leeuwen wrote:
> > >
> > >> For the result from foo() you must somewhere define attributes
> > >> (names).
> > >> Where? In CREATE FUNCTION statement? Possible must be:
> > >>
> > >> select name1, name2 from foo() where name1 > 10;
> > >
> > > Yes, optimal would imho also be if the foo() somehow had access to the
> > > where restriction, so it could only produce output, that the
> > > higher level is interested in, very cool. This would be extremely
> > > useful for me. Very hard to implement, or even find an appropriate
> > > interface for though.
> >
> > You could easily implement it *in* the function foo IMHO. Since the
> > function does some black magic to create the result set to begin with, you
> > can change it to use parameters:
> >
> > select name1, name2 from foo(10, NULL, NULL) where name1 > 10;
>
> The function execution (data reading, etc) is almost last thing in the
> path-of-query. The parser, planner and others parts of PG must already
> knows enough information about a "relation" foo(). I don't know how much
> is intimate idea about this (Tom?), but somewhere in the pg_class /
> pg_attribute must be something about foo() result. (*IMHO* of course:)
>
> I can't imagine that foo() builts on-the-fly arbitrary attributes.
>
> By the way, what permissions? For select (view) we can do GRANT/REVOKE,
> and for select * from foo()? For standard tables it's in the
> pg_class.relacl. IMHO solution is add foo() to pg_class and mark here
> oid of function foo() from pg_proc, and attributes definition store
> to pg_attribute -- everything as for standard table. The source for
> this information must be from CREATE FUNCTION statement, like:
>
> CREATE FUNCTION foo RETURNS( name1 int, name2 text) ....;
>
> If the foo is in the pg_class you can do "GRANT ... ON foo";

I'm planning to require return type to be a existing pg_type already. The
problem with your idea is question if you have two functions (for example)
foo(timestamp) and foo(int4), you must embed the types into relname, and
that's ugly.

Once its possible to control permission to execute a function via GRANT,
it solves the grant problem for function-as-tablesource

-alex