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

Andreas

Wed, 27 Jun 2001 10:35:23 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: functions returning records
> 
>>  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;


Wed, 27 Jun 2001 10:56:43 +0200 (CEST)
"Reinoud van Leeuwen" <reinoud@xs4all.nl>
Re: AW: functions returning records
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";
 

			Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 27 Jun 2001 11:39:57 +0200
Karel Zak <zakkr@zf.jcu.cz>
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


Wed, 27 Jun 2001 06:54:27 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: AW: functions returning records
On Wed, Jun 27, 2001 at 06:54:27AM -0400, Alex Pilosov wrote:
> 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

 Sure, nobody wants to works with something other than is in the 
pg_type.

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

 Good point. First, you needn't work with types, bacause function oid 
is unique for foo(timestamp) and foo(int4). You can work with function
oid. But this is not important. 

 The important thing is that in the PostgreSQL is already resolved very 
simular problem. We can define function with same names, unique must
be function_name + arguments_types. Why not add same thing for tables and 
allows to define as unique table_name + table_type (where table_type
is 'standard table', 'foo() table' and in future may be some other 
special type of table). 
 The parser detect type of table very easy -- 'foo' vs. 'foo()'. 

 IMHO very important is how add new feature and use it together with
old feature.
 
> Once its possible to control permission to execute a function via GRANT,
> it solves the grant problem for function-as-tablesource
 
 The permissions system was an example only. If you add "foo()-tables"
as something what needs special usage and care you probably found more
problems. For example, what show command '\d' in the psql client, how
relation show pg_access ..etc? 

				Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 27 Jun 2001 14:09:38 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: AW: functions returning records
On Wed, 27 Jun 2001, Karel Zak wrote:

>  Sure, nobody wants to works with something other than is in the 
> pg_type.
> 
> > 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.
> 
>  Good point. First, you needn't work with types, bacause function oid 
> is unique for foo(timestamp) and foo(int4). You can work with function
> oid. But this is not important. 
That's not nice. GRANT ALL ON FOO_231234 where 231234 is OID of foo(int4)?
ew.

>  The important thing is that in the PostgreSQL is already resolved very 
> simular problem. We can define function with same names, unique must
> be function_name + arguments_types. Why not add same thing for tables and 
> allows to define as unique table_name + table_type (where table_type
> is 'standard table', 'foo() table' and in future may be some other 
> special type of table). 
>  The parser detect type of table very easy -- 'foo' vs. 'foo()'. 
This is a little bit better, but, results in following syntax:
GRANT SELECT ON FOO(int4). I'm not sure if this really makes sense. Its
not a select permission, its an execute permission on a function, and
should be handled when/where execute permission is checked.

Its not hard to implement (just change what parser thinks relation is),
but I'm sure will conflict with _something_.

>  IMHO very important is how add new feature and use it together with
> old feature.
>  
> > Once its possible to control permission to execute a function via GRANT,
> > it solves the grant problem for function-as-tablesource
>  
>  The permissions system was an example only. If you add "foo()-tables"
> as something what needs special usage and care you probably found more
> problems. For example, what show command '\d' in the psql client, how
> relation show pg_access ..etc? 
\df

Its a function, not a relation. You can do a lot of things to a relation
(such as define rules, triggers, constraints), which do not make any sense
for a function. The function may be used as a table-source, but it does
not make it a table. 

If you can give me a better example than permissions system, I'll surely
reconsider, but currently, I see no use for it...

-alex


Wed, 27 Jun 2001 08:42:07 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: AW: functions returning records
On Wed, Jun 27, 2001 at 08:42:07AM -0400, Alex Pilosov wrote:
> On Wed, 27 Jun 2001, Karel Zak wrote:

> This is a little bit better, but, results in following syntax:
> GRANT SELECT ON FOO(int4). I'm not sure if this really makes sense. Its
> not a select permission, its an execute permission on a function, and

 And if we will have select permission for columns? 

> should be handled when/where execute permission is checked.
> 
> Its not hard to implement (just change what parser thinks relation is),
> but I'm sure will conflict with _something_.
> 
> >  IMHO very important is how add new feature and use it together with
> > old feature.
> >  
> > > Once its possible to control permission to execute a function via GRANT,
> > > it solves the grant problem for function-as-tablesource
> >  
> >  The permissions system was an example only. If you add "foo()-tables"
> > as something what needs special usage and care you probably found more
> > problems. For example, what show command '\d' in the psql client, how
> > relation show pg_access ..etc? 
> \df

 And list of attributes of foo()?
 
> Its a function, not a relation. You can do a lot of things to a relation
> (such as define rules, triggers, constraints), which do not make any sense

 Say with me: it isn't a function, its a function that returning records
and we will use it in same possition as standard table only. The other
usage donsn't exist for this.

 I want wring out from foo()-tables most what is possible (like 
permissions, rules, views). IMHO it's correct requirement :-)

					Karel
-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 27 Jun 2001 15:06:27 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: AW: functions returning records
On Wed, 27 Jun 2001, Karel Zak wrote:

> On Wed, Jun 27, 2001 at 08:42:07AM -0400, Alex Pilosov wrote:
> > On Wed, 27 Jun 2001, Karel Zak wrote:
> 
> > This is a little bit better, but, results in following syntax:
> > GRANT SELECT ON FOO(int4). I'm not sure if this really makes sense. Its
> > not a select permission, its an execute permission on a function, and
> 
>  And if we will have select permission for columns? 
Function returns a tuple. To me, it really makes no sense "this user can
see this attribute of a tuple, but not the other one". 

> > >  
> > >  The permissions system was an example only. If you add "foo()-tables"
> > > as something what needs special usage and care you probably found more
> > > problems. For example, what show command '\d' in the psql client, how
> > > relation show pg_access ..etc? 
> > \df
> 
>  And list of attributes of foo()?
Foo returns type x. \dt x.
>  
> > Its a function, not a relation. You can do a lot of things to a relation
> > (such as define rules, triggers, constraints), which do not make any sense
> 
>  Say with me: it isn't a function, its a function that returning records
> and we will use it in same possition as standard table only. The other
> usage donsn't exist for this.
> 
>  I want wring out from foo()-tables most what is possible (like 
> permissions, rules, views). IMHO it's correct requirement :-)
permissions -- see above
rules -- how? 'create rule blah on select from foo(int4) do instead select
from baz()'? Sorry, that's just too strange for me :)
views -- why not. Create view bar as select * from foo() [1]

Actually, now that I think about it, your idea is essentially creation of
a view automatically when the function returning setof record is created. 
I don't think its a good idea. If you really want to pretend its a
table/view, then create such a view [1]. 

-alex



Wed, 27 Jun 2001 09:30:04 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: AW: functions returning records