Thread: Re: PL/PgSQL discussion

Re: PL/PgSQL discussion

From
Hannu Krosing
Date:
jwieck@debis.com (Jan Wieck) wrote:

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

I think it is a time from connect to disconnect, which currently equals to backends
lifetime, but may in future be shorter, if we will implement a backend pool for
quick-starting servers.

> And should global variables be visible by
>     more  than one function?

How are they global then ?

I think that global variables are something like DateStyle is now.

> I vote for NO! In that case we need
>     something like packages of functions that share globals.

Or we need local temporary tables.

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

I think that subblock should also be used as the extent of IF, FOR ... LOOP, WHILE and
other such statements.

Then we would not need the END IF, END LOOP etc.

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

And vice versa : block comment delimiters can be commented out by --

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

How hard would it bet to have named parameters, or why must we use alias?

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

What can <expr> be?

Possibilities: null, single value, record, recordset

AFAIK, recordsets are usually returned by more strange constructs, like haveing some
kinds of breakpoints inside the loop that either returns a record or some EOF token.

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

Perhaps PL/PgSQL should have some notation for specifying immediate lists of other kinds
of values as well,perhaps like ['aa','bb','cc'], so that one cold loop over not only
integers. Perhaps even with type specifiers, like [::time '22.01', '13.47.05', '15.20']

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

Keep up the nice work!

Hannu


Re: PL/PgSQL discussion

From
jwieck@debis.com (Jan Wieck)
Date:
Hannu Krosing wrote:
>
> jwieck@debis.com (Jan Wieck) wrote:
>
> >     Someone gave a hint about global variables existing during  a
> >     session.   What  is  a  session  than?  One  transaction? The
> >     backends lifetime?
>
> I think it is a time from connect to disconnect, which currently equals to backends
> lifetime, but may in future be shorter, if we will implement a backend pool for
> quick-starting servers.

    Hmmm  -  how  does  a language handler then notice that a new
    session began?

> >
> >         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).
>
> I think that subblock should also be used as the extent of IF, FOR ... LOOP, WHILE and
> other such statements.
>
> Then we would not need the END IF, END LOOP etc.

    The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
    Oracles   PL/SQL   documentation.  I  could  also  live  with
    BEGIN...END, but what is it good for to be different?

>
> >
> >
> >     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.
>
> And vice versa : block comment delimiters can be commented out by --

    Right - works already :-)

>
> >
> >
> >     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.
>
> How hard would it bet to have named parameters, or why must we use alias?

    That isn't subject to the PL  handler.  All  the  PL  handler
    knows about the function is in pg_proc and pg_type. There are
    no parameter names, and that's what the ALIAS idea came from.

    If we sometimes implement a new function call interface, this
    might be possible. Some details about what I have in mind:

        Add a field to pg_proc that tells the  backend  the  call
        interface the function uses.

        Create   a  new  catalog  pg_parameter,  that  holds  the
        parameter names  and  other  information  (like  notnull,
        atttypmod etc.). So a function can be defined to expect a
        VARCHAR(20) NOT NULL.

        The new call interface then hands out more information to
        the  function  than  now.  It's  the  functions  Oid, the
        parameter Datums, a character array telling which of  the
        Datums  are  NULL  and  the  usual bool pointer where the
        function can tell that it's return value is NULL.

> >         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.
>
> What can <expr> be?
>
> Possibilities: null, single value, record, recordset
>
> AFAIK, recordsets are usually returned by more strange constructs, like haveing some
> kinds of breakpoints inside the loop that either returns a record or some EOF token.
>

    Currently  only  'null'  and  'single  value'.  The  executor
    doesn't  accept anything else for non-sql language functions.
    PL functions are treated by the executor like 'C'  functions.

> >                     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.
>
> Perhaps PL/PgSQL should have some notation for specifying immediate lists of other kinds
> of values as well,perhaps like ['aa','bb','cc'], so that one cold loop over not only
> integers. Perhaps even with type specifiers, like [::time '22.01', '13.47.05', '15.20']

    Good issue. Could be done.

>
> Keep up the nice work!
>
> Hannu
>
>

    Already  reached  the  point  of  no  return.  The first tiny
    function ran without problems:

        CREATE FUNCTION f1(int4, int4) RETURNS int4 AS '
            BEGIN
                RETURN $1 + $2;
            END;
        ' LANGUAGE 'plpgsql';

    I set up a little test table with 2  int4  fields  containing
    some  thousand  records. Then I wrote equivalent functions in
    'sql', 'plpgsql' and 'pltcl'.   The  execution  times  for  a
    query

        SELECT sum(func(a, b)) FROM t1;

    are:

        Builtin SQL language    100%
        PL/Tcl                  180%
        PL/pgSQL                230%

    PL/Tcl is slower than builtin SQL because the internals of it
    require the two parameters to be converted to their  external
    representation,  than  calling the Tcl interpreter who parses
    them back to numbers, calculates the result,  returns  it  as
    string and then it's parsed back to int4 internal value.

    In  the PL/pgSQL case I haven't expected that big performance
    loss.  The calculation is internally done with a saved  query
    plan (made on the first call) that does a

        SELECT $1 + $2

    with  two  int4  parameters.  This  is  exactly  what the SQL
    version of the above does!  And >95% of  the  execution  time
    for  the  function  call  are  spent  in  SPI_execp().  Since
    SPI_execp()  calls  ExecutorRun()  directly,  I   think   the
    querydesc  creation and/or plan copying on each invocation is
    the time  consuming  part.  I  assume  that  there  are  some
    optimizable corners in SPI where we can gain more speed. So I
    continue with PL/pgSQL as it is now and speed it up later  by
    tuning SPI.

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

Re: PL/PgSQL discussion

From
Hannu Krosing
Date:
Jan Wieck wrote:

> Hannu Krosing wrote:
> >
> > jwieck@debis.com (Jan Wieck) wrote:
> >
> > >     Someone gave a hint about global variables existing during  a
> > >     session.   What  is  a  session  than?  One  transaction? The
> > >     backends lifetime?
> >
> > I think it is a time from connect to disconnect, which currently equals to backends
> > lifetime, but may in future be shorter, if we will implement a backend pool for
> > quick-starting servers.
>
>     Hmmm  -  how  does  a language handler then notice that a new
>     session began?

Probably by defining the reset_session or init_session functions for the language handler and
calling it at the connect time.

> > >
> > >         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).
> >
> > I think that subblock should also be used as the extent of IF, FOR ... LOOP, WHILE and
> > other such statements.
> >
> > Then we would not need the END IF, END LOOP etc.
>
>     The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
>     Oracles   PL/SQL   documentation.  I  could  also  live  with
>     BEGIN...END, but what is it good for to be different?

then again we don't need the block delimiters ;)

> > >
> > >     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.
> >
> > How hard would it bet to have named parameters, or why must we use alias?
>
>     That isn't subject to the PL  handler.  All  the  PL  handler
>     knows about the function is in pg_proc and pg_type. There are
>     no parameter names, and that's what the ALIAS idea came from.

I just meant them as an automatic way to declare and use aliases for $N, like for example C
does currently.

The calling function does not know the local names of called function in C either.

>     If we sometimes implement a new function call interface, this
>     might be possible. Some details about what I have in mind:
>
>         Add a field to pg_proc that tells the  backend  the  call
>         interface the function uses.
>
>         Create   a  new  catalog  pg_parameter,  that  holds  the
>         parameter names  and  other  information  (like  notnull,
>         atttypmod etc.). So a function can be defined to expect a
>         VARCHAR(20) NOT NULL.

This again can be done by automatically rewriting these to additional statements to check for
this as first things in the function body. It makes only a theoretical difference if the error
is reportid before the call or just after it.

>     Currently  only  'null'  and  'single  value'.  The  executor
>     doesn't  accept anything else for non-sql language functions.
>     PL functions are treated by the executor like 'C'  functions.

Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
scope of this tutorial', but not impossible ;)

---------

Hannu


Re: PL/PgSQL discussion

From
jwieck@debis.com (Jan Wieck)
Date:
Hannu Krosing wrote:
>
> Jan Wieck wrote:
>
> > Hannu Krosing wrote:
> > >
> > > jwieck@debis.com (Jan Wieck) wrote:
> > >
> >
> >     Hmmm  -  how  does  a language handler then notice that a new
> >     session began?
>
> Probably by defining the reset_session or init_session functions for the language handler and
> calling it at the connect time.

    Not in the current implementation of the backend/pl_handler
    interface. But a global variable SessionId might help.

> > > Then we would not need the END IF, END LOOP etc.
> >
> >     The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
> >     Oracles   PL/SQL   documentation.  I  could  also  live  with
> >     BEGIN...END, but what is it good for to be different?
>
> then again we don't need the block delimiters ;)

    Forgotten  in  the  1st  draft.  Variables are initialized to
    their default values every time a block/subblock is  entered.
    Not only when the function is entered.

      ...
      FOR i IN 1..10 LOOP
        DECLARE
          flag bool DEFAULT false;
          n integer DEFAULT 0;
        BEGIN
        ...
        END;
      END LOOP;
      ...

> >         Create   a  new  catalog  pg_parameter,  that  holds  the
> >         parameter names  and  other  information  (like  notnull,
> >         atttypmod etc.). So a function can be defined to expect a
> >         VARCHAR(20) NOT NULL.
>
> This again can be done by automatically rewriting these to additional statements to check for
> this as first things in the function body. It makes only a theoretical difference if the error
> is reportid before the call or just after it.

    But this rewriting must be done when the function is created.
    At this time, the pl_handler and it's parser  aren't  called.
    It  is  done  by  the  backends  main  parser.  For  loadable
    procedural languages, the main parser doesn't  know  anything
    about the languages syntax or if the string given after AS is
    a program text at all. It only creates the pg_proc tuple.

>
> >     Currently  only  'null'  and  'single  value'.  The  executor
> >     doesn't  accept anything else for non-sql language functions.
> >     PL functions are treated by the executor like 'C'  functions.
>
> Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
> scope of this tutorial', but not impossible ;)

    I  tried  that  really  hard  with  no  luck.  And   I   know
    ExecMakeFunctionResult()   pretty  good.  But  I'll  give  it
    another shot when PL/pgSQL reached a  state  where  it  makes
    sense.


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

Re: PL/PgSQL discussion

From
Hannu Krosing
Date:
Jan Wieck wrote:

> > >     Hmmm  -  how  does  a language handler then notice that a new
> > >     session began?
> >
> > Probably by defining the reset_session or init_session functions for the language handler and
> > calling it at the connect time.
>
>     Not in the current implementation of the backend/pl_handler
>     interface. But a global variable SessionId might help.

Neither do we have backend pooling now.

> > > > Then we would not need the END IF, END LOOP etc.
> > >
> > >     The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
> > >     Oracles   PL/SQL   documentation.  I  could  also  live  with
> > >     BEGIN...END, but what is it good for to be different?
> >
> > then again we don't need the block delimiters ;)
>
>     Forgotten  in  the  1st  draft.  Variables are initialized to
>     their default values every time a block/subblock is  entered.
>     Not only when the function is entered.
>
>       ...
>       FOR i IN 1..10 LOOP
>         DECLARE
>           flag bool DEFAULT false;
>           n integer DEFAULT 0;
>         BEGIN
>         ...
>         END;
>       END LOOP;
>       ...

Ok, this makes sense.

> > This again can be done by automatically rewriting these to additional statements to check for
> > this as first things in the function body. It makes only a theoretical difference if the error
> > is reportid before the call or just after it.
>
>     But this rewriting must be done when the function is created.
>     At this time, the pl_handler and it's parser  aren't  called.

Ok, I understand it now.

<name> ALIAS FOR $n;

is to overcome the split between the two parser.

>     It  is  done  by  the  backends  main  parser.  For  loadable
>     procedural languages, the main parser doesn't  know  anything
>     about the languages syntax or if the string given after AS is
>     a program text at all. It only creates the pg_proc tuple.

And another thing:

Does'nt the Oracle PL/SQL have both input and input/output parameters ?

It's more than a year since i last used it but I think that this is the case.

Hannu


Re: [HACKERS] Re: PL/PgSQL discussion

From
dg@illustra.com (David Gould)
Date:
>
> jwieck@debis.com (Jan Wieck) wrote:
>
> >     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 !

A question. Will it be possible to call functions from the PL/pgSQL? This
covers a fair bit, for example to evaluate expressions and casts etc as
well as calling user functions.

Or was this covered and I just missed it somehow?

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

Re: [HACKERS] Re: PL/PgSQL discussion

From
dg@illustra.com (David Gould)
Date:
jwieck@debis.com (Jan Wieck) wrote:
> Hannu Krosing wrote:
> > jwieck@debis.com (Jan Wieck) wrote:
...
> > I think it is a time from connect to disconnect, which currently equals to backends
> > lifetime, but may in future be shorter, if we will implement a backend pool for
> > quick-starting servers.
>
>     Hmmm  -  how  does  a language handler then notice that a new
>     session began?

Either when the backend exits, or is re-initted to go back to the idle pool.


> > >         PL/pgSQL is a block oriented language. A block is defined as
> > >             [<<label>>]
> > >             [DECLARE
> > >                 -- declarations]
> > >             BEGIN
> > >                 -- statements
> > >             END;
...
> > I think that subblock should also be used as the extent of IF, FOR ... LOOP, WHILE and
> > other such statements.
> > Then we would not need the END IF, END LOOP etc.
>
>     The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
>     Oracles   PL/SQL   documentation.  I  could  also  live  with
>     BEGIN...END, but what is it good for to be different?

I am not convinced that imitating Oracle is necessarily the best possible way
to proceed. If there is a standard, and I think there is at least a proposal
(called PSM, if memory serves), we might (but only might) want to do that.
Otherwise, either lets steal something worth stealing or invent something
better than whats out there.


> > How hard would it bet to have named parameters, or why must we use alias?
>
>     That isn't subject to the PL  handler.  All  the  PL  handler
>     knows about the function is in pg_proc and pg_type. There are
>     no parameter names, and that's what the ALIAS idea came from.
...
> > >         Returning from the function
> > >
> > >                 RETURN <expr>;
> > >
> > What can <expr> be?
> >
> > Possibilities: null, single value, record, recordset
> >
> > AFAIK, recordsets are usually returned by more strange constructs, like haveing some
> > kinds of breakpoints inside the loop that either returns a record or some EOF token.
> >
>     Currently  only  'null'  and  'single  value'.  The  executor
>     doesn't  accept anything else for non-sql language functions.
>     PL functions are treated by the executor like 'C'  functions.

This limitation suggests that PL/pgSQL functions want to _be_ SQL functions,
not 'C' functions. Handy to be able to write:

begin
    if $1 = "totals" then
       select sum(qty), sum(qty) * price from sales_orders where ...;
    else if $1 = "details"
       select qty, price from sales_orders where ...

Ok, lousy example, but I have seen this kind of thing in apps ...


>     Already  reached  the  point  of  no  return.  The first tiny
>     function ran without problems:

I am impressed.

>         CREATE FUNCTION f1(int4, int4) RETURNS int4 AS '
>             BEGIN
>                 RETURN $1 + $2;
>             END;
>         ' LANGUAGE 'plpgsql';
>
>     I set up a little test table with 2  int4  fields  containing
>     some  thousand  records. Then I wrote equivalent functions in
>     'sql', 'plpgsql' and 'pltcl'.   The  execution  times  for  a
>     query
>
>         SELECT sum(func(a, b)) FROM t1;
>
>     are:
>
>         Builtin SQL language    100%
>         PL/Tcl                  180%
>         PL/pgSQL                230%
>
>     PL/Tcl is slower than builtin SQL because the internals of it
>     require the two parameters to be converted to their  external
>     representation,  than  calling the Tcl interpreter who parses
>     them back to numbers, calculates the result,  returns  it  as
>     string and then it's parsed back to int4 internal value.
>
>     In  the PL/pgSQL case I haven't expected that big performance
>     loss.  The calculation is internally done with a saved  query
>     plan (made on the first call) that does a
>
>         SELECT $1 + $2
>
>     with  two  int4  parameters.  This  is  exactly  what the SQL
>     version of the above does!  And >95% of  the  execution  time
>     for  the  function  call  are  spent  in  SPI_execp().  Since
>     SPI_execp()  calls  ExecutorRun()  directly,  I   think   the
>     querydesc  creation and/or plan copying on each invocation is
>     the time  consuming  part.  I  assume  that  there  are  some
>     optimizable corners in SPI where we can gain more speed. So I
>     continue with PL/pgSQL as it is now and speed it up later  by
>     tuning SPI.

I think you might want to do some profiling to find where the time is
really going. Not to throw water on your parade, but being slower than
tcl is something of an achievement ;-).

I wish I could be of more use than this, but that will have to wait
until I get some time to look over the postgres code a bit. It is similar
to Illustra in many ways, but the two lines branched apart quite a while
ago and have gone in different directions especially in the language areas.


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.

Re: [HACKERS] Re: PL/PgSQL discussion

From
jwieck@debis.com (Jan Wieck)
Date:
Hannu Krosing wrote:
>
> And another thing:
>
> Does'nt the Oracle PL/SQL have both input and input/output parameters ?
>
> It's more than a year since i last used it but I think that this is the case.

    Right,  they  have.  But  PostgreSQL  doesn't and so PL/pgSQL
    can't.  PL/pgSQL cannot do any thing that  a  C  function  in
    PostgreSQL cannot do. The PL handler is written in C and that
    is what the executor calls instead of the compiled  function.
    From  the  backends point of view, a PL handler is just one C
    function with a bunch of personalities.


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

Re: [HACKERS] Re: PL/PgSQL discussion

From
jwieck@debis.com (Jan Wieck)
Date:
David Gould wrote:
>
> >
> > jwieck@debis.com (Jan Wieck) wrote:
> >
> > >     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 !
>
> A question. Will it be possible to call functions from the PL/pgSQL? This
> covers a fair bit, for example to evaluate expressions and casts etc as
> well as calling user functions.
>
> Or was this covered and I just missed it somehow?

    It is covered automagically by the way I'm implementing the
    assignement. The assign code internally does a SELECT. And
    thus:

        n := count(*) from t1;

    works (even if it's far from standard I think).

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