Re: AW: [HACKERS] Rule system

> > It would require two extensions to PL/pgSQL:
> >
> > A 'RENAME oldname newname' in the declarations part so
> > the internal trigger procedures record 'old' can be
> > renamed to 'o'.
>
> Actually, since this does not give added functionality, I guess always using the
> keywords old and new would be ok (get rid of "current" though, it is unclear and has
> another SQL92 meaning).

But since it was soooo easy I did it already :-)

And yesss - I don't have current at all. Only new and old.

>
> >
> > Implementation of referencing record/rowtype.* extends to
> > a comma separated list of parameters when manipulating
> > the insert statement. My current implementation of
> > PL/pgSQL can only substitute a single
> > variable/recordfiled/rowfield into one parameter.
>
> This is a feature, that would make life easier ;-) (low priority)

Agree - low priority. So I leave this feature for later.

>
> The real problem I have would be procedures that return more than one column (or an opaque row/or rows),
> or nothing at all.
> Like:
>
> create function onename returns char(16), char(16) -- or would it have to be returns opaque ?
> as 'select "Hans", "Moser"' language 'sql';
>
> insert into employee (fname, lname) values (onename()); -- or
> insert into employee (fname, lname) select onename();
> >
> > These two wouldn't be that complicated. And it would have a
> > real advantage. As you see above, I must double any ' because
> > the function body is written inside of ''s. It's a pain - and
> > here's a solution to get out of it.
>
> That is why I suggested a while ago to keyword begin and end for plpgsql,
> then everything between begin and end would be plsql automatically without the quotes.
> This would then be much like Oracle PL/SQL.
>
> Something like:
> create function delrow (int highestsalary) as begin
> delete from employee where sal > highestsalary; -- or :highestsalary or $highestsalary
> end;
>
> >
> > If anyone is happy with this, I would release PL/pgSQL after
> > 6.4 and make the required changes in the parser.
> >
>
> Actually for me the possibility to return an opaque row from a function
> would currently be the most important enhancement of all.
> Somewhere the code that handles the "returns opaque" case is missing code to
> handle the case where a whole tuple is returned.
>
> Andreas

I think I should clearify some details.

PL/pgSQL sticks right into the current implementation of
loadable procedural languages. It has it's own, completely
independent scanner and parser. PostgreSQL's CREATE FUNCTION
simply creates a pg_proc entry with prosrc attribute set to
the functions text.

When the function (or trigger procedure which are functions
too) is invoked, the PL/pgSQL shared object is loaded and
called. This now reads the pg_proc tuple and compiles the
prosrc (only done on the first call of the function).

Then the PL/pgSQL executor (totally different beast from the
PostgreSQL main executor) runs over the precompiled
statements. Many of the statements will invoke calls to the
SPI manager (any expression evaluation and DB access).

Inside of PL/pgSQL BEGIN..END; can be nested like in Oracle's
PL/SQL to build blocks of statements and different targets to
where EXIT will jump out. So if we change CREATE FUNCTION to
accept DECLARE/BEGIN..END; instead of '..' too, then it must
count the nesting level until it reached 0 again. All that
including the initial DECLARE/BEGIN and the final END; is the
string for the prosrc attribute of pg_proc. Needless to say
that comments and literal strings can contain any of these
keywords that don't have to be counted then.

Now we all know enough. So back to discussion.

All restrictions that C functions have are also restrictions
to loadable procedural language functions. And beeing unable
to return multiple results, tuples or tuplesets is such a
restriction to C functions. Thus, we have to extend the
function call interface of PostgreSQL at all. But not before
6.4!

I would really like any kind of function (C, PL/Tcl,
PL/pgSQL, more?) to be able to return tuples or tuplesets.
And I took a look at the executor some time ago to see how
this might be done. But boy, this is far more to do than it
looks like first. Function calls that return tuples have
target lists and they don't return only a tuple, they return
a tuple table slot containing a projection tuple (if I
remember correctly). And functions returning tuple sets are
one of the most ugly thing I've ever seen. In the case of a
set return the executors func node is manipulated so the scan
node from the last sql statement in the function can be used
in subsequent calls to get the next row instead of invoking
the function again. No procedural language that requires back
control after a

RETURN value AND RESUME;

can procude a scan node that could fit into this model,
except that it creates a temp table, inserting all the tuples
to return there, and finally returning something like a
seqscan over the temp table so they get pulled back. Hmmm -
thinking about this is nice - might be a solution :-)

I don't know if it will be possible to give all functions the
ability to return tuples or sets. So let's assume for now
that it doesn't work (I'll really try hard after 6.4).

But then again, even if functions stay that restricted, what
do we need as rule functionality? Up to now I only have all
kinds of INSEAD rules on the statement level on my list.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #