Re: AW: [HACKERS] Rule system - Mailing list pgsql-hackers

> >    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@debis.com (Jan Wieck) #