Hi,

    as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
    procedural language. As far as I'm now I  have  a  pl_handler
    with  an  independent  flex/bison  parser  that  can  parse a
    rudimentary implementation of the language. The next step  is
    to  start  on the PL/pgSQL executor and look if the generated
    instruction tree can be used (up to now the  pl_handler  only
    dumps the instruction tree and returns a 0 Datum.

    If  that  works  I'll  expand  the scanner/parser to the full
    PL/plSQL language including trigger procedures.

    But I would like to have some discussion on language  itself.
    So  I wrote down what I had in mind. The document is appended
    below.

    Please comment/suggest !

    Someone gave a hint about global variables existing during  a
    session.   What  is  a  session  than?  One  transaction? The
    backends lifetime?  And should global variables be visible by
    more  than one function?  I vote for NO! In that case we need
    something like packages of functions that share globals.


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) #


                                PL/pgSQL
             A procedural language for the PostgreSQL RDBMS
                                1st draft

                      Jan Wieck <jwieck@debis.com>



    Notice

        This  document  is  for  the postgresql-hackers community for
        completing  the  syntax  specification   of   PL/pgSQL.   The
        extension module described here doesn't exist up to now!


    Preface

        PL/pgSQL  is  a procedural language based on SQL designed for
        the PostgreSQL database system.

        The extensibility features of PostgreSQL are mostly based  on
        the  ability  to  define  functions  for  various operations.
        Functions could have been written in PostgreSQL's SQL dialect
        or  in the C programming language. Functions written in C are
        compiled into a shared object  and  loaded  by  the  database
        backend  process  on  demand.   Also  the trigger features of
        PostgreSQL are based on functions but required the use of the
        C language.

        Since  version  6.3  PostgreSQL  supports  the  definition of
        procedural languages. In the case of a  function  or  trigger
        procedure  defined in a procedural language, the database has
        no builtin knowlege how to  interpret  the  functions  source
        text. Instead, the function and trigger calls are passed into
        a handler that  knows  the  details  of  the  language.   The
        handler  itself is function compiled into a shared object and
        loaded on demand.


    Overview

        The PL/pgSQL language is case insensitive. All  keywords  and
        identifiers can be used in upper-/lowercase mixed.

        PL/pgSQL is a block oriented language. A block is defined as

            [<<label>>]
            [DECLARE
                -- declarations]
            BEGIN
                -- statements
            END;

        There  can  be  any  number  of  subblocks  in the statements
        section of a block. Subblocks can be used to  hide  variables
        from  outside a block of statements (see Scope and visability
        below).


    Comments

        There are two types of comments in PL/pgSQL.  A  double  dash
        '--'  starts a comment that extends to the end of the line. A
        '/*' starts a block comment that extends to  the  next  '*/'.
        Block comments cannot be nested, but double dash comments can
        be enclosed into a block comment.


    Declarations

        All variables, rows and records  used  in  a  block  or  it's
        subblocks must be declared in the declarations section of the
        block.   The   parameters   given   to   the   function   are
        automatically  declared  with  the usual identifiers $n.  The
        declarations have the following syntax:

            <name> [CONSTANT] <type> [NOT NULL]
                                     [DEFAULT | := <value>];

                Declares a variable of the  specified  type.  If  the
                variable is declared as CONSTANT, the value cannot be
                changed. If NOT NULL is specified, an assignment of a
                NULL  value  results  in  a  runtime error. Since the
                default value of a variable is the  SQL  NULL  value,
                all  variables  declared as NOT NULL must also have a
                default value.

                The default value is evaluated at the actual function
                call. So assigning 'now' to an abstime varable causes
                the variable to have the time of the actual  function
                call, not when the function was compiled.

            <name> <class>%ROWTYPE;

                Declares a row with the structure of the given class.
                Class must be an existing table- or viewname  of  the
                database.  The  fields of the row are accessed in the
                dot notation. Parameters  to  a  procedure  could  be
                tuple   types.   In   that   case  the  corresponding
                identifier $n  will  be  a  rowtype.  Only  the  user
                attributes  and  the oid of a tuple are accessible in
                the row. There must be  no  whitespaces  between  the
                classname, the percent and the ROWTYPE keyword.

            <name> RECORD;

                Records  are  similar  to  rowtypes, but they have no
                predefined structure and it's impossible to assign  a
                value  into them. They are used in selections and FOR
                loops to hold one actual database tuple from a select
                operation.  One  and  the  same record can be used in
                different selections (but not in nested ones).

            <name> ALIAS FOR $n;

                For better readability of the code it's  possible  to
                define  an  alias  for  a positional parameter to the
                function.

    Datatypes

        The type of a variable can be any of the existing data  types
        of the database. <type> above is defined as:

                postgesql-basetype
            or  variable%TYPE
            or  rowtype.field%TYPE
            or  class.field%TYPE

        As  for the rowtype declaration, there must be no whitespaces
        between the classname, the percent and the TYPE keyword.

    Expressions

        All expressions used in  PL/pgSQL  statements  are  processed
        using  the  backends  executor. Since even a constant looking
        expression  can  have  a  totally  different  meaning  for  a
        particular data type (as 'now' for abstime), it is impossible
        for the PL/pgSQL parser  to  identify  real  constant  values
        other than the NULL keyword. The expressions are evaluated by
        internally executing a query

            SELECT <expr>

        over the  SPI  manager.  In  the  expression,  occurences  of
        variable  identifiers  are  substituted by parameters and the
        actual values from the variables are passed to  the  executor
        as  query  parameters. All the expressions used in a PL/pgSQL
        function are only prepared and saved once.

    Statements

        Anything not understood by the parser as specified below will
        be  put  into a query and sent down to the database engine to
        execute.  The resulting query should not return any data.

        Assignment

            An assignment of a value to a variable or  rowtype  field
            is written as:

                <identifier> := <expr>;

            If  the  expressions  result  data type doesn't match the
            variables data type, or the variables atttypmod value  is
            known  (as  for  char(20)),  the  result  value  will  be
            implicitly casted by  the  PL/pgSQL  executor  using  the
            result  types  output-  and  the  variables  type  input-
            functions.  Note that this could  potentially  result  in
            runtime errors generated by the types input functions.

            An  assignment  of  a complete selection into a record or
            rowtype can be done as:

                SELECT targetlist INTO <recname|rowname> FROM fromlist;

            If a rowtype is used as target, the  selected  targetlist
            must  exactly  match  the  structure  of the rowtype or a
            runtime error occurs.  The fromlist can  be  followed  by
            any  valid qualification, grouping, sorting etc. There is
            a  special  condition  [NOT]  FOUND  that  can  be   used
            immediately  after a SELECT INTO to check if the data has
            been found.

                SELECT * INTO myrec FROM EMP WHERE empname = myname;
                IF NOT FOUND THEN
                    ELOG ERROR 'employee %s not found' myname;
                END IF;

            In addition, the select statement must  not  return  more
            that  one  row.  If multiple rows are returned, a runtime
            error will be generated.


        Returning from the function

                RETURN <expr>;

            The function terminates and the value of <expr>  will  be
            returned  to  the  upper executor.  The return value of a
            function cannot be undefined.  If control reaches the end
            of  the  toplevel block of the function without hitting a
            RETURN statement, a runtime error will occur.


        Aborting and messages

            As indicated above there is an ELOG  statement  that  can
            throw messages into the PostgreSQL elog mechanism.

                ELOG level 'format' [identifiers];

            Inside the format, only %s might be used as a placeholder
            for  the  following  identifiers.  The  identifiers  must
            specify an existing variable or row/record field.


        Conditionals

                IF <expr> THEN
                    -- statements
                [ELSE
                    -- statements]
                END IF;

            The  expression  <expr> must return a value that at least
            can be casted into a boolean.


        Loops

            There are multiple types of loops.

                [<<label>>]
                LOOP
                    -- statements
                END LOOP;

            An unconditional loop that must be terminated  explicitly
            by  an  EXIT statement. The optional label can be used by
            EXIT statements of nested loops to specify which level of
            nesting should be terminated.

                [<<label>>]
                WHILE <expr> LOOP
                    -- statements
                END LOOP;

            A  conditional  loop  that  is  executed  as  long as the
            evaluation of <expr> returns true.

                [<<label>>]
                FOR <name> IN [REVERSE] <expr>..<expr> LOOP
                    -- statements
                END LOOP.

            A loop that iterates over a range of integer values.  The
            variable  <name> is automatically created as type integer
            and exists only inside  the  loop.  The  two  expressions
            giving  the  lower  and  upper  bound  of  the  range are
            evaluated only when entering the loop. The iteration step
            is 1.

                FOR <recname|rowname> IN <select_clause> LOOP
                    -- statements
                END LOOP;

            The record or row is assigned all the rows resulting from
            the select clause and the statements executed  for  each.
            If  the  loop  is  terminated with an EXIT statement, the
            last accessed row is still accessible in  the  record  or
            rowtype.

                EXIT [label] [WHEN <expr>];

            If  no  label given, the innermost loop is terminated and
            the statement following END LOOP  is  executed  next.  If
            label is given, it must be the label of the current or an
            upper level of nested loops or blocks.   Then  the  named
            loop  or  block  is terminated and control continues with
            the statement after the loops/blocks corresponding END.


Thu, 12 Mar 1998 16:38:32 +0100 (MET)
jwieck@debis.com (Jan Wieck)
PL/pgSQL - for discussion
>     as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
>     procedural language.
>     Please comment/suggest !
>     Someone gave a hint about global variables existing during  a
>     session.   What  is  a  session  than?  One  transaction? The
>     backends lifetime?  And should global variables be visible by
>     more  than one function?  I vote for NO! In that case we need
>     something like packages of functions that share globals.

This looks nice. SQL92 (and presumably SQL3) has the concept of global
and local, temporary and permanent, tables. I believe that it also has
the concept of variables with the same possible combinations of
behaviors, but I'm not finding that in my books at the moment.

Clearly if we have these features in the backend someday, then there
would need to be hooks for your PL to use also. No need for you to
provide these if the backend doesn't help you imho.

I haven't had a chance to read the main portion of your document yet...

                      - Tom

Thu, 12 Mar 1998 16:21:08 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] PL/pgSQL - for discussion
> 
> Hi,
> 
>     as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
>     procedural language. As far as I'm now I  have  a  pl_handler
>     with  an  independent  flex/bison  parser  that  can  parse a
>     rudimentary implementation of the language. The next step  is
>     to  start  on the PL/pgSQL executor and look if the generated
>     instruction tree can be used (up to now the  pl_handler  only
>     dumps the instruction tree and returns a 0 Datum.
> 
>     If  that  works  I'll  expand  the scanner/parser to the full
>     PL/plSQL language including trigger procedures.
> 
>     But I would like to have some discussion on language  itself.
>     So  I wrote down what I had in mind. The document is appended
>     below.
> 
>     Please comment/suggest !

Gee, it looks really nice.  I have never used such an advanced language
INSIDE a database engine.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 12 Mar 1998 12:40:44 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] PL/pgSQL - for discussion
Jan Wieck wrote:
> 
> Hi,
> 
>     as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
>     procedural language. As far as I'm now I  have  a  pl_handler
>     with  an  independent  flex/bison  parser  that  can  parse a
>     rudimentary implementation of the language. The next step  is
>     to  start  on the PL/pgSQL executor and look if the generated
>     instruction tree can be used (up to now the  pl_handler  only
>     dumps the instruction tree and returns a 0 Datum.
> 
>     If  that  works  I'll  expand  the scanner/parser to the full
>     PL/plSQL language including trigger procedures.
> 
>     But I would like to have some discussion on language  itself.
>     So  I wrote down what I had in mind. The document is appended
>     below.
> 
>     Please comment/suggest !
> 
>     Someone gave a hint about global variables existing during  a
>     session.   What  is  a  session  than?  One  transaction? The
>     backends lifetime?  And should global variables be visible by
>     more  than one function?  I vote for NO! In that case we need
>     something like packages of functions that share globals.
> 
> 
> Jan

This looks good.  

I especially like the "for x in select ..." part, it looks a lot more
elegant than cursors, but we might want to provide a cursor with a
"get next row" and "get previous row" function, as the for loop only
goes one way.

Another suggestion related to parameters:

>             <name> ALIAS FOR $n;
> 
>                 For better readability of the code it's  possible  to
>                 define  an  alias  for  a positional parameter to the
>                 function.
> 

What is the defined behavior if the user leaves out this parameter?
Do we generate a runtime error?  If I might suggest the following:

<name> ALIAS FOR $n;
sets up name as an alias for $n, name is null if that parameter was
not given.

<name> REQUIRED ALIAS FOR $n;
sets up name as an alias for $n, generate a runtime error if that
parameter was not given.

Actually, an assignment might be a better way to do this.  I.E. Define
foo as int not null, assign $2 to foo and if there is an error, the user is notified.

Ocie

Thu, 12 Mar 1998 12:16:13 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] PL/pgSQL - for discussion

I haven't read the PL/SQL proposal yet so please do not take this as
criticism of the proposal. It is just that I have sometimes wondered (having
used and maintained a couple of them) if there is a real need to invent
another procedural language inside a dbms. Who really needs yet another
language that only works in certain special circumstances? Why not just
adapt an existing language implementation and graft SQL integration into
it? For example in Perl:

sub find_prospects_to_contact ()
   # note that sql select looks like a file handle/split() combo to perl
   while (<select ($name, $phone) from prospects p, 
              where prospect.interests ~= /computer/ or /electronics/;
          >) {
      # some stuff to be done per row
      ...
      if ($should_contact) {
         insert into contacts_todo values ($name, $phone);
      }
   }


Of course there are probably a zillion great reasons why this would be hard
or the wrong thing to do, but still...

 Wouldn't it be cool?

-dg


David Gould            dg@illustra.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - I realize now that irony has no place in business communications.


Thu, 12 Mar 1998 13:31:33 -0800 (PST)
dg@illustra.com (David Gould)
Re: [HACKERS] PL/pgSQL - for discussion
David Gould wrote:
> 
> I haven't read the PL/SQL proposal yet so please do not take this as
> criticism of the proposal. It is just that I have sometimes wondered (having
> used and maintained a couple of them) if there is a real need to invent
> another procedural language inside a dbms. Who really needs yet another
> language that only works in certain special circumstances?

But Jan has already adopted an existing language interpreter (i.e. TCL).
Now he speaks about server side programing using native SQL.

Actually existance of SQL server programming in SQL database seems to be
quite expected feature. You may consider that most SQL developers
doesn't really need other languages but SQL, so it's not inventing
another language. It's just a wider, more flexible implementation of
internal SQL.

Mike

-- 
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND

Thu, 12 Mar 1998 22:54:20 +0100
Michal Mosiewicz <mimo@interdata.com.pl>
Re: [HACKERS] PL/pgSQL - for discussion
Jan Wieck wrote:
> 
> Hi,
> 
>     as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
>     procedural language. As far as I'm now I  have  a  pl_handler
>     with  an  independent  flex/bison  parser  that  can  parse a
>     rudimentary implementation of the language. The next step  is
>     to  start  on the PL/pgSQL executor and look if the generated
>     instruction tree can be used (up to now the  pl_handler  only
>     dumps the instruction tree and returns a 0 Datum.
> 
>     If  that  works  I'll  expand  the scanner/parser to the full
>     PL/plSQL language including trigger procedures.

Why PL/pgSQL should be loadable PL? Why not built-in ?
Would it be possible to add dirrect support for PL/pgSQL syntax
to current parser ?
Typing procedure body inside ' is not nice thing, imho.

>     Someone gave a hint about global variables existing during  a
>     session.   What  is  a  session  than?  One  transaction? The
>     backends lifetime?  And should global variables be visible by
      ^^^^^^^^^^^^^^^^^
This.

>     more  than one function?  I vote for NO! In that case we need
>     something like packages of functions that share globals.

Let's leave packages for future, but why session-level variables
shouldn't be visible inside procedures right now? 

> 
>         PL/pgSQL is a block oriented language. A block is defined as
> 
>             [<<label>>]
>             [DECLARE
>                 -- declarations]
>             BEGIN
>                 -- statements
>             END;

Someday we'll have nested transactions...
How about disallow using BEGIN/END as transaction control statements
right now ?
START/COMMIT/ROLLBACK/ABORT and nothing more...

>             <name> <class>%ROWTYPE;
> 
>                 Declares a row with the structure of the given class.
>                 Class must be an existing table- or viewname  of  the
>                 database.  The  fields of the row are accessed in the
>                 dot notation. Parameters  to  a  procedure  could  be
>                 tuple   types.   In   that   case  the  corresponding
>                 identifier $n  will  be  a  rowtype.  Only  the  user
>                 attributes  and  the oid of a tuple are accessible in
>                 the row. There must be  no  whitespaces  between  the
>                 classname, the percent and the ROWTYPE keyword.
> 
>             <name> RECORD;
> 
>                 Records  are  similar  to  rowtypes, but they have no
>                 predefined structure and it's impossible to assign  a
>                 value  into them. They are used in selections and FOR
>                 loops to hold one actual database tuple from a select
>                 operation.  One  and  the  same record can be used in
>                 different selections (but not in nested ones).

Do we really need in both ROWTYPE & RECORD ?
I would get rid of RECORD and let ROWTYPE variables be 
'with yet undefined type of row' (make <class> optional). More of that,
why not treat ROWTYPE like structures in C and let the following:

name %ROWTYPE {a	int4, b text};

?

>                 SELECT * INTO myrec FROM EMP WHERE empname = myname;
                                ^^^^^                          ^^^^^^
How about $-prefix ?

>             As indicated above there is an ELOG  statement  that  can
>             throw messages into the PostgreSQL elog mechanism.
> 
>                 ELOG level 'format' [identifiers];
                  ^^^^^^^^^^
NO, pls - too postgres-ish! Just let ABORT to have 'format' etc and add 
PRINT (or something like this) to put some messages to application (via NOTICE).
What are used in Oracle, Sybase etc here ?

Vadim

Fri, 13 Mar 1998 11:40:07 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] PL/pgSQL - for discussion

Ocie wrote:
>
> This looks good.
>
> I especially like the "for x in select ..." part, it looks a lot more
> elegant than cursors, but we might want to provide a cursor with a
> "get next row" and "get previous row" function, as the for loop only
> goes one way.

    We  don't  have  real  cursors  up to now. The SPI_exec() and
    SPI_execp() functions  return  the  complete  set  of  tuples
    selected.   And  I'm not sure if that what PostgreSQL calls a
    cursor can be used in the backend over SPI. It requires named
    portals  and  them in turn require a transaction block (BEGIN
    ... COMMIT).  But I think it would be easy to build something
    that looks like cursors on top of the complete set of tuples.

>
> Another suggestion related to parameters:
>
> >             <name> ALIAS FOR $n;
> >
> >                 For better readability of the code it's  possible  to
> >                 define  an  alias  for  a positional parameter to the
> >                 function.
> >
>
> What is the defined behavior if the user leaves out this parameter?
> Do we generate a runtime error?  If I might suggest the following:
>
> <name> ALIAS FOR $n;
> sets up name as an alias for $n, name is null if that parameter was
> not given.

    The backends main parser chooses functions not only by  name.
    The  number  and  datatypes of the given parameters must also
    match  (function  overloading  -   possible   with   our   PL
    implementation).  If  a  query execution reaches the function
    call, be sure that all parameters are given.

    I thought about it just as  a  way  to  make  the  code  more
    readable.  The parameters might also be accessed by the usual
    $n notation.  So if you have

        empname ALIAS FOR $n;

    in the declarations, empname and $n are identical. Thats  how
    I understand the word ALIAS.

>
> <name> REQUIRED ALIAS FOR $n;
> sets up name as an alias for $n, generate a runtime error if that
> parameter was not given.
>
> Actually, an assignment might be a better way to do this.  I.E. Define
> foo as int not null, assign $2 to foo and if there is an error, the user is notified.

    Does  make  sense.  But for sake a function cannot identify a
    null value in one of the parameters.  The  passed  in  isNull
    flag  only says that one of all parameters is null. Not which
    one. We have to change this someday, and I have already  some
    ideas on that. But that's another topic.


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) #



Fri, 13 Mar 1998 09:59:36 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] PL/pgSQL - for discussion

Mike wrote:
>
> David Gould wrote:
> >
> > I haven't read the PL/SQL proposal yet so please do not take this as
> > criticism of the proposal. It is just that I have sometimes wondered (having
> > used and maintained a couple of them) if there is a real need to invent
> > another procedural language inside a dbms. Who really needs yet another
> > language that only works in certain special circumstances?
>
> But Jan has already adopted an existing language interpreter (i.e. TCL).
> Now he speaks about server side programing using native SQL.

    For version 6.3 look into .../pgsql/src/pl/tcl.

>
> Actually existance of SQL server programming in SQL database seems to be
> quite expected feature. You may consider that most SQL developers
> doesn't really need other languages but SQL, so it's not inventing
> another language. It's just a wider, more flexible implementation of
> internal SQL.

    Even  if  "most  SQL  developers don't need (or know) another
    language" is reason enough for  an  SQL  based  PL,  my  main
    reason is another one.

    PL/Tcl  at least requires that the Tcl library got built on a
    system.  And I have tested only that it works with Tcl7.5 and
    Tcl8.0.   I  expect  that  the PL/perl implementation (I hope
    Brett McCormick is still working on that) will  need  a  perl
    library too.

    So  there  is  no  PL  implementation  up  to  now,  that  is
    independent from another software package. PL/pgSQL will  be!
    PL/pgSQL  will  be  the first language that can get installed
    and  enabled  by  default  and  then  be  available  in   all
    PostgreSQL installations.


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) #



Fri, 13 Mar 1998 10:31:01 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] PL/pgSQL - for discussion

Vadim wrote:
>
> Jan Wieck wrote:
> >
> > Hi,
> >
> >     as  I  proposed,  I'm  now  starting on the PL/pgSQL loadable
> >     procedural language. As far as I'm now I  have  a  pl_handler
> >     with  an  independent  flex/bison  parser  that  can  parse a
> >     rudimentary implementation of the language. The next step  is
> >     to  start  on the PL/pgSQL executor and look if the generated
> >     instruction tree can be used (up to now the  pl_handler  only
> >     dumps the instruction tree and returns a 0 Datum.
> >
> >     If  that  works  I'll  expand  the scanner/parser to the full
> >     PL/plSQL language including trigger procedures.
>
> Why PL/pgSQL should be loadable PL? Why not built-in ?
> Would it be possible to add dirrect support for PL/pgSQL syntax
> to current parser ?
> Typing procedure body inside ' is not nice thing, imho.

    Well,  PL/pgSQL  could  be  compiled  in  and  the pl_handler
    function  and  language  tuples   set   at   bootstrap.   But
    incorporating  the parser into the backends main parser isn't
    nesseccary then either. Not that I think it's impossible, but
    the current main parser is complex enough for me.

    The  typing  of  the  procedure body inside of ' is damned. I
    know :-) I think it might be possible to allow {} or the like
    to  be  used  instead and then only quote \} inside the body.
    This stuff might be easy done in the scanner (haven't  looked
    at the code yet).

>
> >     Someone gave a hint about global variables existing during  a
> >     session.   What  is  a  session  than?  One  transaction? The
> >     backends lifetime?  And should global variables be visible by
>       ^^^^^^^^^^^^^^^^^
> This.

    OK.

>
> >     more  than one function?  I vote for NO! In that case we need
> >     something like packages of functions that share globals.
>
> Let's leave packages for future, but why session-level variables
> shouldn't be visible inside procedures right now?

    For  security. At least I would like the visibility of global
    variables depend on the functions owner. So users A and B can
    use the same global name in their functions but the variables
    are different.

>
> >
> >         PL/pgSQL is a block oriented language. A block is defined as
> >
> >             [<<label>>]
> >             [DECLARE
> >                 -- declarations]
> >             BEGIN
> >                 -- statements
> >             END;
>
> Someday we'll have nested transactions...
> How about disallow using BEGIN/END as transaction control statements
> right now ?
> START/COMMIT/ROLLBACK/ABORT and nothing more...

    Right now!

>
> Do we really need in both ROWTYPE & RECORD ?
> I would get rid of RECORD and let ROWTYPE variables be
> 'with yet undefined type of row' (make <class> optional). More of that,
> why not treat ROWTYPE like structures in C and let the following:
>
> name %ROWTYPE {a  int4, b text};

    Hmmm. Or doing it the Oracle way

        DECLARE
          TYPE myrectype IS RECORD (
            field1  integer NOT NULL,
            field2  text);

          myrec   myrectype;
        BEGIN
          ...
        END

    But I would like to let the RECORD of  unspecified  structure
    in.  It doesn't need much declarations typing.

>
> ?
>
> >                 SELECT * INTO myrec FROM EMP WHERE empname = myname;
>                                 ^^^^^                          ^^^^^^
> How about $-prefix ?

    I  don't  like  the  $'s.  But  I  have seen the problem that
    without blowing up my parser I cannot do it  the  oracle  way
    where  a  field  name  of  a  selected table precedes a local
    varname and the  local  varname  if  identical  to  a  tables
    fieldname  must be prefixed with the label of the block. This
    is what Oracle does:

        <<outer>>
        DECLARE
          emp emp%ROWTYPE;
          empname  emp.empname%TYPE
          salary   emp.salary%TYPE
        BEGIN
          ...
          SELECT * INTO outer.emp FROM emp WHERE empname = outer.empname;
          --            ^^^^^^^^^      ^^^       ^^^^^^^   ^^^^^^^^^^^^^
          --            PLs rowtype    table     table-    PLs variable
          --                                     field

          salary := emp.salary;
          -- ^^^^^^^^^^^^^^^^^
          -- Outside of SELECT stmt - all identifiers in PL

          ...
        END


>
> >             As indicated above there is an ELOG  statement  that  can
> >             throw messages into the PostgreSQL elog mechanism.
> >
> >                 ELOG level 'format' [identifiers];
>                   ^^^^^^^^^^
> NO, pls - too postgres-ish! Just let ABORT to have 'format' etc and add
> PRINT (or something like this) to put some messages to application (via NOTICE).
> What are used in Oracle, Sybase etc here ?

    Oracle uses RAISE EXCEPTION ... with some numbers  specifying
    the message in the message catalog and other information.

    What about

        RAISE EXCEPTION 'format' [identifiers];     -- elog(ERROR, ...)
        RAISE NOTICE 'format' [identifiers];        -- elog(NOTICE, ...)
        RAISE DEBUG 'format' [identifiers];         -- elog(DEBUG, ...)

    The  first  is somewhat compatible and the two otheres can be
    easyly  commented  out.  Since  the  language   is   somewhat
    PostgreSQL   specific   anyway  (arguments  are  unnamed  and
    identified by position with $n), PL procedures must be ported
    when  moving to another DB. But who ever wants to use another
    DB, once he used PostgreSQL?


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) #



Fri, 13 Mar 1998 13:10:30 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] PL/pgSQL - for discussion
Jan and Vadim (I think) discuss:
> >
> > Why PL/pgSQL should be loadable PL? Why not built-in ?
> > Would it be possible to add dirrect support for PL/pgSQL syntax
> > to current parser ?
...
>     Well,  PL/pgSQL  could  be  compiled  in  and  the pl_handler
>     function  and  language  tuples   set   at   bootstrap.   But
>     incorporating  the parser into the backends main parser isn't
>     nesseccary then either. Not that I think it's impossible, but
>     the current main parser is complex enough for me.

I agree, for a language this simple, it would be nice to have it
in the main parser. This might not be too hard since the existing statements
are not really changing, only some new ones are added. eg:

Select_Stmt := SELECT _name_list FROM _from_clause WHERE _where_clause_
...

+ If_Stmt := IF _expression_ THEN _statement_list END
+ Loop_Stmt := ...

Or whatever the grammar looks like.

A bit harder is that the executor now has to sequence through the statements
and handle branches and storing variables etc.

> > >     Someone gave a hint about global variables existing during  a
> > >     session.   What  is  a  session  than?  One  transaction? The
> > >     backends lifetime?  And should global variables be visible by
> >       ^^^^^^^^^^^^^^^^^
> > This.

Agree. 

>     OK.
> 
> > >     more  than one function?  I vote for NO! In that case we need
> > >     something like packages of functions that share globals.
> >
> > Let's leave packages for future, but why session-level variables
> > shouldn't be visible inside procedures right now?
> 
>     For  security. At least I would like the visibility of global
>     variables depend on the functions owner. So users A and B can
>     use the same global name in their functions but the variables
>     are different.

Not a problem. If the global variables are global only to the session they
can be stored in allocated memory, not in the shared memory. Automatically
then they are private to the user of that session and are destroyed when
the session ends.

> > >             [<<label>>]
> > >             [DECLARE
> > >                 -- declarations]
> > >             BEGIN
> > >                 -- statements
> > >             END;
> >
> > Someday we'll have nested transactions...
> > How about disallow using BEGIN/END as transaction control statements
> > right now ?
> > START/COMMIT/ROLLBACK/ABORT and nothing more...
> 
>     Right now!

Hmmm, I like BEGIN TRAN/END TRAN/ABORT TRAN. I suppose there is a standard
we should be following... Also, we probably should not start breaking
existing applications, users get very fussy about that.
 
> > >             throw messages into the PostgreSQL elog mechanism.
> > >
> > >                 ELOG level 'format' [identifiers];
> >                   ^^^^^^^^^^
> > NO, pls - too postgres-ish! Just let ABORT to have 'format' etc and add
> > PRINT (or something like this) to put some messages to application (via NOTICE).

What is wrong with ELOG? It seems concise and does what is wanted, yes?
Why add syntax?

-dg

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - I realize now that irony has no place in business communications.


Fri, 13 Mar 1998 23:41:44 -0800 (PST)
dg@illustra.com (David Gould)
Re: [HACKERS] PL/pgSQL - for discussion