functions returning records

On Tue, 26 Jun 2001 17:11:47 -0400 (EDT), you wrote:

I started thinking about Tom's idea to implement functions as table
source.

To me, it seems that a very few changes are necessary:
a) parser must be changed to allow functioncall to be a table_ref
(easy)

b) when a Query node is generated out of such a call "select * from foo()"
it should be almost identical to one generated out of "select * from
(select * from foo)" with one distinction: list of query attributes should
be completed based on return type of foo().

c) executor should support execution of such Query node, properly
extracting things out of function's return value and placing them into
result attributes.

Coming from a Sybase environment I would love to have functions return
a result set. A few things to think of:
1: will it be possible to return multiple result sets? (in Sybase any
select statement that is not redirected to variables or a table goes
to the client, so it is quite common to do multiple selects). Does the
postgresql client library support this?

2: will it be possible to put a single result set in a table.
Something like "resultfunction (argument) INTO TABLENAME" or "INSERT
INTO TABLENAME resultfunction(argument)

--
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen reinoud@xs4all.nl
http://www.xs4all.nl/~reinoud
__________________________________________________

On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:

Coming from a Sybase environment I would love to have functions return
a result set. A few things to think of:
1: will it be possible to return multiple result sets? (in Sybase any
select statement that is not redirected to variables or a table goes
to the client, so it is quite common to do multiple selects). Does the
postgresql client library support this?

No, libpq protocol cannot support that. This is really a sybasism, as good
as it is, no other database supports anything like that.

2: will it be possible to put a single result set in a table.
Something like "resultfunction (argument) INTO TABLENAME" or "INSERT
INTO TABLENAME resultfunction(argument)

It will be, but syntax will be:
select * into tablename from resultfunction(arg)
insert into tablename select * from resultfunction(arg)

(I.E. resultfunction must be in the 'from' clause)

-alex

On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:

I started thinking about Tom's idea to implement functions as table
source.

To me, it seems that a very few changes are necessary:
a) parser must be changed to allow functioncall to be a table_ref
(easy)

b) when a Query node is generated out of such a call "select * from foo()"
it should be almost identical to one generated out of "select * from
(select * from foo)" with one distinction: list of query attributes should
be completed based on return type of foo().

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;

What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

c) executor should support execution of such Query node, properly
extracting things out of function's return value and placing them into
result attributes.

d) changes in fmgr

e) SPI support for table building/filling inside foo()

IMHO very cool and nice feature, but not easy for imlementation.

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

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

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;

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;

Yes, but this is only an answer to a limited scope of the problem at hand,
and the user who types the select (or uses a warehouse tool) needs substantial
additional knowledge on how to efficiently construct such a query.

In my setup the function would be hidden by a view.

Andreas

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

On Wed, 27 Jun 2001, Karel Zak wrote:

On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:

I started thinking about Tom's idea to implement functions as table
source.

To me, it seems that a very few changes are necessary:
a) parser must be changed to allow functioncall to be a table_ref
(easy)

b) when a Query node is generated out of such a call "select * from foo()"
it should be almost identical to one generated out of "select * from
(select * from foo)" with one distinction: list of query attributes should
be completed based on return type of foo().

For the result from foo() you must somewhere define attributes (names).
Where? In CREATE FUNCTION statement? Possible must be:

Function must be returning an existing reltype. I understand its a major
restriction, but I can't think of a better way.

select name1, name2 from foo() where name1 > 10;

What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

Pointer to heaptuple. We can get to tupdesc for that tuple by looking up
its prorettype.

c) executor should support execution of such Query node, properly
extracting things out of function's return value and placing them into
result attributes.

d) changes in fmgr

Don't think that's necessary, but I guess I'll find out when I try it :)

e) SPI support for table building/filling inside foo()

As far as SPI is concerned, its the same as current: function returning
records must return pointer to HeapTuple containing the record.

On Wed, 27 Jun 2001, Zeugswetter Andreas SB 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;

Yes, but this is only an answer to a limited scope of the problem at hand,
and the user who types the select (or uses a warehouse tool) needs substantial
additional knowledge on how to efficiently construct such a query.

In my setup the function would be hidden by a view.

Its a different problem. Functions returning tables do just that, return
tables, they won't care just what from that table you need. Exposing
pieces of optimizer to your function doesn't seem to me like a great
idea...

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

In my setup the function would be hidden by a view.

Its a different problem. Functions returning tables do just that, return
tables, they won't care just what from that table you need. Exposing
pieces of optimizer to your function doesn't seem to me like a great
idea...

Ok, I think i need to go into a little more detail to explain.
My function needs to construct a table from the where condition.
If no where condition is present the result set would be near infinite
in size (all possible permutations of all possible field values
e.g. 2^32 for a table with one int column).

The function answers queries about rows that are not in the table,
but the result is based on rows that are in the table and computed
by a neural net.

Andreas

For the result from foo() you must somewhere define attributes (names).
Where? In CREATE FUNCTION statement? Possible must be:

Function must be returning an existing reltype. I understand its a major
restriction, but I can't think of a better way.

Yup, that's how Informix does it. It has a "create row type" command,
so you don't actually need a table.

select name1, name2 from foo() where name1 > 10;

What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

Pointer to heaptuple. We can get to tupdesc for that tuple by looking up
its prorettype.

But the question is how you get the next row. Do you return a null terminated
array of heaptuples ?

Imho to allow this to be efficient, there would need to be some mechanism,
that would allow the function to return the result in small blocks (e.g. each row)
(similar to a heap access), else you would be limited to return
values, that fit into memory, or fit on temporary disk storage, and do
work that might not even be required, because the client only fetches the
first row.

Andreas

On Wed, 27 Jun 2001, Zeugswetter Andreas SB wrote:

In my setup the function would be hidden by a view.

Its a different problem. Functions returning tables do just that, return
tables, they won't care just what from that table you need. Exposing
pieces of optimizer to your function doesn't seem to me like a great
idea...

Ok, I think i need to go into a little more detail to explain.
My function needs to construct a table from the where condition.
If no where condition is present the result set would be near infinite
in size (all possible permutations of all possible field values
e.g. 2^32 for a table with one int column).

The function answers queries about rows that are not in the table,
but the result is based on rows that are in the table and computed
by a neural net.

This is pretty s[l]ick. Unfortunately, SQL doesn't know about
lazy-evaluation for functions, and its kind of a different problem from
one I would like to solve, but I agree, maybe some day, there could be a
[documented] way for an SPI function to peek at the query conditions in
the context it was called from.

It is _probably_ already possible to do that by looking up the execution
stack somehow, but its definitely not a documented way, and you must be
able to extract your information from a (Query *) node...

-alex

Zeugswetter Andreas SB 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;

Yes, but this is only an answer to a limited scope of the problem at hand,
and the user who types the select (or uses a warehouse tool) needs substantial
additional knowledge on how to efficiently construct such a query.

In my setup the function would be hidden by a view.

I have done a lot of playing around with this sort of thing to get my search
engine working.

While functions returning rows would be cool, and something I'd like to see. I
think the functionality, if not the syntax, you are looking for is already in
postgres 7.1.x. Here is an example: (Actual code at bottom of message)

select n1, n2 from (select foo1(10) as n1, foo2() as n2) as fubar ;

The trick seems to be, to have the first function return a 'setof' results.
Have the foo2() function return the next column of foo1()'s current result.

Here is the output:

markw=# select foo1(10) as n1, foo2() as n2;
n1 | n2
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)

Or you can create a synthetic table at query time, called fubar:

markw=# select * from (select foo1(10) as n1, foo2() as n2) as fubar;
n1 | n2
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)

Now, I'm not sure if it is documented that the first function gets called
first, or that next functions get called after each result of a result "setof"
but it seem logical that they should, and I would like to lobby that this
becomes an "official" behavior of the function manager and the execution
processing.

<<<<<<<<<<<<< code >>>>>>>>>>>>>>

static int count;
static int curr;

Datum foo1(PG_FUNCTION_ARGS);
Datum foo2(PG_FUNCTION_ARGS);

Datum foo1(PG_FUNCTION_ARGS)
{
if(!fcinfo->resultinfo)
{
elog(ERROR, "Not called with fcinfo");
PG_RETURN_NULL();
}
if(!count)
{
count = PG_GETARG_INT32(0);
curr = 1;
}
else
curr++;

if(curr <= count)
{
ReturnSetInfo *rsi = (ReturnSetInfo *)fcinfo->resultinfo;
rsi->isDone = ExprMultipleResult;
PG_RETURN_INT32(curr);
}
else
{
ReturnSetInfo *rsi ;
curr=0;
count=0;
rsi = (ReturnSetInfo *)fcinfo->resultinfo;
rsi->isDone = ExprEndResult ;
}
PG_RETURN_NULL();
}

Datum foo2(PG_FUNCTION_ARGS)
{
if(curr <= count)
PG_RETURN_INT32(curr);
else
PG_RETURN_INT32(42);
}

SQL:

create function foo1( int4)
returns setof int4
as '/usr/local/lib/templ.so', 'foo1'
language 'c' ;

create function foo2()
returns int4
as '/usr/local/lib/templ.so', 'foo2'
language 'c' ;

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

On Wed, 27 Jun 2001, Zeugswetter Andreas SB wrote:

select name1, name2 from foo() where name1 > 10;

What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

Pointer to heaptuple. We can get to tupdesc for that tuple by looking up
its prorettype.

But the question is how you get the next row. Do you return a null terminated
array of heaptuples ?

Imho to allow this to be efficient, there would need to be some mechanism,
that would allow the function to return the result in small blocks (e.g. each row)
(similar to a heap access), else you would be limited to return
values, that fit into memory, or fit on temporary disk storage, and do
work that might not even be required, because the client only fetches the
first row.

I haven't thought of this yet, but its a good point. I think I'll find out
what's involved when I write code for it. :)

-alex

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

On Wed, 27 Jun 2001, mlw wrote:

While functions returning rows would be cool, and something I'd like
to see. I think the functionality, if not the syntax, you are looking
for is already in postgres 7.1.x. Here is an example: (Actual code at
bottom of message)

Yes, its already possible, but its extremely ugly and nontransparent. I
don't want to create 5 functions to return 5-row tuple, or have to deal
with C SPI to do that. It needs a minor cleanup which is all I'm trying to
do :)

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

Alex Pilosov wrote:

On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:

Coming from a Sybase environment I would love to have functions return
a result set. A few things to think of:
1: will it be possible to return multiple result sets? (in Sybase any
select statement that is not redirected to variables or a table goes
to the client, so it is quite common to do multiple selects). Does the
postgresql client library support this?

No, libpq protocol cannot support that. This is really a sybasism, as good
as it is, no other database supports anything like that.

IIRC the _protocol_ should support it all right, but the current libpq
implementation does not (and the sql queries in functions are not sent
to
client either)

---------------
Hannu

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

Hannu Krosing <hannu@tm.ee> writes:

Alex Pilosov wrote:

On Tue, 26 Jun 2001, Reinoud van Leeuwen wrote:

1: will it be possible to return multiple result sets? (in Sybase any
select statement that is not redirected to variables or a table goes
to the client, so it is quite common to do multiple selects). Does the
postgresql client library support this?

No, libpq protocol cannot support that. This is really a sybasism, as good
as it is, no other database supports anything like that.

IIRC the _protocol_ should support it all right, but the current libpq
implementation does not (and the sql queries in functions are not sent
to client either)

Actually, libpq supports it just fine too, but most clients don't.
You have to use PQsendQuery() and a PQgetResult() loop to deal with
multiple resultsets out of one query. It is possible to see this
happening even today:

PQsendQuery(conn, "SELECT * FROM foo ; SELECT * FROM bar");

while ((res = PQgetResult(conn)))
{
...

Whether it would be a *good idea* to allow standalone SELECTs in
functions to be handled that way is another question. I've got strong
doubts about it. The main problem is that the function call would be
nested inside another SELECT, which means you'd have the problem of
suspending a resultset transmission already in progress. That's *not*
in the protocol, much less libpq, and you wouldn't really want clients
forced to buffer incomplete resultsets anyway. But it could be
supported in procedures (not functions) that are called by some kind of
PERFORM statement, so that there's not a SELECT already in progress when
they are invoked.

regards, tom lane

Karel Zak wrote:

On Tue, Jun 26, 2001 at 05:11:47PM -0400, Alex Pilosov wrote:

I started thinking about Tom's idea to implement functions as table
source.

To me, it seems that a very few changes are necessary:
a) parser must be changed to allow functioncall to be a table_ref
(easy)

b) when a Query node is generated out of such a call "select * from foo()"
it should be almost identical to one generated out of "select * from
(select * from foo)" with one distinction: list of query attributes should
be completed based on return type of foo().

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;

What returns foo()? ...the pointer to HeapTuple or something like this or
pointer to some temp table?

c) executor should support execution of such Query node, properly
extracting things out of function's return value and placing them into
result attributes.

d) changes in fmgr

e) SPI support for table building/filling inside foo()

IMHO very cool and nice feature, but not easy for imlementation.

Good questions - must be because I asked them myself before.
:-)

My idea on that is as follows:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

2. A function that RETURNS SETOF record/table/view is
expected to return a refcursor (which is basically a
portal name - SPI support already in 7.2), who's tupdesc
matches the structure.

3. The Func node for such a function invocation will call
the function with the appropriate arguments to get the
portal, receive the tuples with an internal fetch method
one per invocation (I think another destination is
basically enough) and close the portal at the end.

4. Enhancement of the portal capabilities. A new function
with a tuple descriptor as argument creates a special
portal that simply opens a tuple sink. Another function
stores a tuple there and a third one rewinds the sink and
switches the portal into read mode, so that fetches will
return the tuples again. One format of the tuple sink is
capable of backward moves too, so it'll be totally
transparent.

5. Enhancement of procedural languages that aren't
implemented as state machines (currently all of them) to
use the tuple-sink-portals and implement RETURN AND
RESUME.

This plan reuses alot of existing code and gains IMHO the
most functionality. All portals are implicitly closed at the
end of a transaction. This form of internal portal usage
doesn't require explicit transaction blocks (as of current
7.2 tree). All the neat buffering, segmenting of the tuple
sink code for materializing the result set comes into play.
From the executors POV there is no difference between a
function returning a portal that's a real SELECT, collecting
the data on the fly, or a function materializing the result
set first with RETURN AND RESUME. The tuple structure
returned by a function is not only known at parsetime, but
can be used in other places like for %ROWTYPE in PL/pgSQL.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jan Wieck <JanWieck@Yahoo.com> writes:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity. It would be nice if
we could describe a record type with only pg_type and pg_attribute
entries. I haven't thought about it in detail, but seems like it
could be done if pg_attribute entries are changed to reference
pg_type, not pg_class, rows as their parent. However, this would
break so many existing queries in psql and other clients that it'd
probably be unacceptable :-(

2. A function that RETURNS SETOF record/table/view is
expected to return a refcursor (which is basically a
portal name - SPI support already in 7.2), who's tupdesc
matches the structure.

Otherwise this proposal sounds good. Jan and I talked about it earlier;
one point I recall is that the portal/cursor based approach can
internally support the existing multiple-call implementation of
functions returning sets. That is, when you call the portal to get the
next tuple, it might hand you back a tuple saved from a previous
function call, or it might turn around and call the function again to
get the next tuple.

BTW, once we've had this for a release or two, I'd like to rip out the
existing support for calling functions-returning-sets during SELECT list
evaluation, so that expression evaluation could be simplified and sped
up. But we can wait for people to change over their existing uses
before we do that.

regards, tom lane

On Wed, 27 Jun 2001, Jan Wieck wrote:

My idea on that is as follows:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

Okay

2. A function that RETURNS SETOF record/table/view is
expected to return a refcursor (which is basically a
portal name - SPI support already in 7.2), who's tupdesc
matches the structure.

Okay, but that will break whatever currently written functions which
return setof. Although it could be considered a good thing, as its too
ugly now :)

3. The Func node for such a function invocation will call
the function with the appropriate arguments to get the
portal, receive the tuples with an internal fetch method
one per invocation (I think another destination is
basically enough) and close the portal at the end.

OK

4. Enhancement of the portal capabilities. A new function
with a tuple descriptor as argument creates a special
portal that simply opens a tuple sink. Another function
stores a tuple there and a third one rewinds the sink and
switches the portal into read mode, so that fetches will
return the tuples again. One format of the tuple sink is
capable of backward moves too, so it'll be totally
transparent.

OK

5. Enhancement of procedural languages that aren't
implemented as state machines (currently all of them) to
use the tuple-sink-portals and implement RETURN AND
RESUME.

I'm not sure I understand this one correctly. Could you explain what
you mean here by 'use'?

What is "RETURN AND RESUME"? Do you mean a function that precomputes
entire result set before stuffing it into portal?

This plan reuses alot of existing code and gains IMHO the
most functionality. All portals are implicitly closed at the
end of a transaction. This form of internal portal usage
doesn't require explicit transaction blocks (as of current
7.2 tree). All the neat buffering, segmenting of the tuple
sink code for materializing the result set comes into play.
From the executors POV there is no difference between a
function returning a portal that's a real SELECT, collecting
the data on the fly, or a function materializing the result
set first with RETURN AND RESUME. The tuple structure
returned by a function is not only known at parsetime, but
can be used in other places like for %ROWTYPE in PL/pgSQL.

I think I once again got myself in over my head :) But I'm going to try to
code this thing anyway, with great suggestions from Karel and you....

-alex

Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity. It would be nice if
we could describe a record type with only pg_type and pg_attribute
entries. I haven't thought about it in detail, but seems like it
could be done if pg_attribute entries are changed to reference
pg_type, not pg_class, rows as their parent. However, this would
break so many existing queries in psql and other clients that it'd
probably be unacceptable :-(

It's not THAT ugly for me, and the fact that it's named
"pg_class" instead of "pg_relation" makes some sense all of
the sudden.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Alex Pilosov wrote:

On Wed, 27 Jun 2001, Jan Wieck wrote:

My idea on that is as follows:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

Okay

2. A function that RETURNS SETOF record/table/view is
expected to return a refcursor (which is basically a
portal name - SPI support already in 7.2), who's tupdesc
matches the structure.

Okay, but that will break whatever currently written functions which
return setof. Although it could be considered a good thing, as its too
ugly now :)

Not necessarily. We could as well (as Tom mentioned already)
add another portal enhancement, so that the current "SETOF
tuple" function behaviour is wrapped by a portal. So if you
call a "SETOF tuple" function, the function pointer get's
stored in the portal and the function called on FETCH (or the
internal fetch methods). The distinction on the SQL level
could be done as "RETURNS CURSOR OF ...", don't know how to
layer that into pg_proc yet, but would make it even clearer.

I'm not sure I understand this one correctly. Could you explain what
you mean here by 'use'?

What is "RETURN AND RESUME"? Do you mean a function that precomputes
entire result set before stuffing it into portal?

On the PL/pgSQL level such a function could look like

...
FOR row IN SELECT * FROM mytab LOOP
RETURN (row.a, row.b + row.c) AND RESUME;
END LOOP;
RETURN;

Poor example and could be done better, but you get the idea.
The language handler opens a tuple sink portal for it. On
every loop invocation, one tuple is stuffed into it and on
the final return, the tuple sink is rewound and prepared to
return the tuples. The portal around it controls when to get
rid of the sink, wherever it resides.

These sinks are the place where the sorter for example piles
it's tuples. For small numbers of tuples, they are just held
in main memory. Bigger collections get stuffed into a
tempfile and huge ones even in segmented tempfiles. What's
considered "small" is controlled by the -S option (sort
buffer size). So it's already a runtime option.

I think I once again got myself in over my head :) But I'm going to try to
code this thing anyway, with great suggestions from Karel and you....

Hard training causes sore muscles, unfortunately it's the
only way to gain muscle power - but take a break before you
have a cramp :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity.

I dont think that sequence is any more table-like than record.

And difference between type and class ia also quite debatable in
most languages ;)

Also there seems to be more existing creative use of pg_class - what
does relkind='s' record for pg_variable stand for ?

Otherwise this proposal sounds good. Jan and I talked about it earlier;
one point I recall is that the portal/cursor based approach can
internally support the existing multiple-call implementation of
functions returning sets. That is, when you call the portal to get the
next tuple, it might hand you back a tuple saved from a previous
function call, or it might turn around and call the function again to
get the next tuple.

BTW, once we've had this for a release or two, I'd like to rip out the
existing support for calling functions-returning-sets during SELECT list
evaluation, so that expression evaluation could be simplified and sped
up. But we can wait for people to change over their existing uses
before we do that.

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

---------------
Hannu

Hannu Krosing <hannu@tm.ee> writes:

Tom Lane wrote:

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity.

I dont think that sequence is any more table-like than record.

Oh? It's got storage, it's got columns, you can select from it.

test71=# create sequence myseq;
CREATE
test71=# select * from myseq;
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+------------+-----------+-------------+---------+-----------+-----------
myseq | 1 | 1 | 2147483647 | 1 | 1 | 1 | f | f
(1 row)

Looks pretty table-ish to me.

Also there seems to be more existing creative use of pg_class - what
does relkind='s' record for pg_variable stand for ?

Special system relation. Again, there's storage behind it (at least for
pg_log, I suppose pg_xactlock is a bit of a cheat... but there doesn't
really need to be a pg_class entry for pg_xactlock anyway, and I'm not
sure pg_log needs one either).

However, this is fairly academic considering the backwards-compatibility
downside of changing pg_attribute.attrelid to pg_attribute.atttypid :-(

regards, tom lane

On Thu, 28 Jun 2001, Hannu Krosing wrote:

Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

1. Adding a new relkind that means 'record'. So we use
pg_class, pg_attribute and pg_type as we do for tables
and views to describe a structure.

It seems fairly ugly to have a pg_class entry for something that
isn't a table or even a table-like entity.

I dont think that sequence is any more table-like than record.

And difference between type and class ia also quite debatable in
most languages ;)

Also there seems to be more existing creative use of pg_class - what
does relkind='s' record for pg_variable stand for ?

Otherwise this proposal sounds good. Jan and I talked about it earlier;
one point I recall is that the portal/cursor based approach can
internally support the existing multiple-call implementation of
functions returning sets. That is, when you call the portal to get the
next tuple, it might hand you back a tuple saved from a previous
function call, or it might turn around and call the function again to
get the next tuple.

BTW, once we've had this for a release or two, I'd like to rip out the
existing support for calling functions-returning-sets during SELECT list
evaluation, so that expression evaluation could be simplified and sped
up. But we can wait for people to change over their existing uses
before we do that.

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

-alex

Alex Pilosov wrote:

On Thu, 28 Jun 2001, Hannu Krosing wrote:

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

Don't think so. When the function returns, the call stack
get's destroyed. Jumping back to there - er - the core dump
is not even useful any more. Or did I miss something?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Wed, 27 Jun 2001, Jan Wieck wrote:

Alex Pilosov wrote:

On Thu, 28 Jun 2001, Hannu Krosing wrote:

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

Don't think so. When the function returns, the call stack
get's destroyed. Jumping back to there - er - the core dump
is not even useful any more. Or did I miss something?

Well, it shouldn't return, but instead save the location and longjmp to
SPI_RESUME_jmp location. On a next call, instead of a function call, it
should longjmp back to saved location. I have to admit its more complex
than I originally thought, but probably doable.

-alex

The other thing:

1/ SELECT a, b, c FROM foo();
2/ SELECT a FROM foo();

How result (build and) returns function foo() in example 1/ and 2/ ?

It's bad functions if returns same result for both queries -- because
in example 2/ is wanted only one columns. IMHO function returning
records needs information about wanted result (number of columns, etc).

For example trigger functions has specific information by
"CurrentTriggerData" struct. For functions returning records we can
create special struct too. What?

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

Alex Pilosov wrote:

On Wed, 27 Jun 2001, Jan Wieck wrote:

Alex Pilosov wrote:

On Thu, 28 Jun 2001, Hannu Krosing wrote:

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

Don't think so. When the function returns, the call stack
get's destroyed. Jumping back to there - er - the core dump
is not even useful any more. Or did I miss something?

Well, it shouldn't return, but instead save the location and longjmp to
SPI_RESUME_jmp location. On a next call, instead of a function call, it
should longjmp back to saved location. I have to admit its more complex
than I originally thought, but probably doable.

OK, let's screw it up some more:

SELECT F.a, B.b FROM foo() F, bar() B
WHERE F.a = B.a;

This should normally result in a merge join, so you might get
away with longjmp's. But you get the idea.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

alex@pilosoft.com (Alex Pilosov) writes:

: [...]
: Well, it shouldn't return, but instead save the location and longjmp to
: SPI_RESUME_jmp location. On a next call, instead of a function call, it
: should longjmp back to saved location. I have to admit its more complex
: than I originally thought, but probably doable.

Implementing (what are in effect) co-routines or continuations by
setjmp/longjmp is an inherently non-portable practice. (Think about
how at all SPI_RESUME_jmp *and* the user-defined-function's saved
location could both be valid places to longjmp to at, the same time.)
At the least, you would need some assembly language code, and
heap-allocated stacks. Take a look into what user-level threading
libraries do.

If you went down this avenue, you might decide that a reasonable way
to do this is in fact to rely on first-class threads to contain the
execution context of user-defined functions. You wouldn't have the
concurrency problems normally associated with threads (since the
server would still only activate one thread at a time).

- FChE

Jan Wieck wrote:

Alex Pilosov wrote:

On Wed, 27 Jun 2001, Jan Wieck wrote:

Alex Pilosov wrote:

On Thu, 28 Jun 2001, Hannu Krosing wrote:

How hard would it be to turn this around and implement RETURN AND
CONTINUE
for at least PL/PGSQL, and possibly C/Perl/Python ... ?

Cannot talk about plpgsql, but for c this would be probably implemented
with setjmp and with perl with goto. Probably not very complex.

Don't think so. When the function returns, the call stack
get's destroyed. Jumping back to there - er - the core dump
is not even useful any more. Or did I miss something?

Well, it shouldn't return, but instead save the location and longjmp to
SPI_RESUME_jmp location. On a next call, instead of a function call, it
should longjmp back to saved location. I have to admit its more complex
than I originally thought, but probably doable.

OK, let's screw it up some more:

SELECT F.a, B.b FROM foo() F, bar() B
WHERE F.a = B.a;

This should normally result in a merge join, so you might get
away with longjmp's. But you get the idea.

On a third thought, you don't get anywhere with longjmp's.
You have a call stack, do a setjmp() saving the stack
pointer. Then you call the function, do another setjmp() here
and do the longjmp() to #1. This restores the saved stack
pointer, so at the very first time you do any other function
call (lib calls included), you corrupt the stack frame at the
current stack pointer position. If you later jump back to
setjmp() #2 location, you'll not be able to return.

You can only drop stack frames safely, you can't add them
back, they aren't saved.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Thu, 28 Jun 2001, Jan Wieck wrote:

On a third thought, you don't get anywhere with longjmp's.
You have a call stack, do a setjmp() saving the stack
pointer. Then you call the function, do another setjmp() here
and do the longjmp() to #1. This restores the saved stack
pointer, so at the very first time you do any other function
call (lib calls included), you corrupt the stack frame at the
current stack pointer position. If you later jump back to
setjmp() #2 location, you'll not be able to return.

You can only drop stack frames safely, you can't add them
back, they aren't saved.

True. I withdraw the idea.

See this for a s[l]ick implementation of coroutines in C:

http://www.chiark.greenend.org.uk/~sgtatham/coroutines.html

(essentially a replacement for set of gotos)

Tis ugly, but it should work (tm).