Re: Re: Requests for Development - Mailing list pgsql-sql

KuroiNeko wrote:
> > I wonder if there couldn't borrowed some code from Interbase which has
> > full featured stored procedures - at least  it was told to me that it has
> > ...
>
>  Well, I have some hands-on experience  with IB, don't know whether this is
> perfectly relevant, but here goes....
>  Indeed, stored  procedures in  IB can do  what's called  `returning record
> sets' in  this thread. This  is helpfull  when tuples restriction  is based
> upon  condition  that is  not  easy/possible  to  formulate in  SQL  (where
> clause).  On the  other hand,  IB has  two different  ways to  call an  SP:
> execute  procedure  for `singleton'  SPs  and  select for  those  returning
> multiple tuples.
>  However, IB supports  only its own SP language. It's  pretty much complete
> and well thought and implemented, but if you want an SP in PERL, you're out
> of luck.
>  What I'd really  like to see is  `pre-compiled' SPs in PGSQL.  IB has this
> feature (SPs are converted to BLR when DDL statement is executed), not sure
> about PGSQL.  I've noticed  that language-specific errors  in SPs  are only
> reported by  PGSQL when SP is  executed, so I suggest  that interpreter (eg
> for PL/PGSQL) is called each time.
   Not entirely true.
   PL/Tcl  has  "spi_exec" as well as "spi_prepare/spi_execp". A   function is  only  sourced  into  the  interpreter
once per   session  (backend  lifetime) and has a global upvar called GB   where it could store prepared plans at it's
firstcall. Since   version  8.0  Tcl  uses  a  bytecode  compiler  and  will not   interpret the real source text again
andagain.
 
   PL/pgSQL parses the entire function body at first  call  (per   backend).   But  the  SPI querystrings for all the
statements  aren't parsed at that time.  It uses SPI_prepare()  only  for   expressions and queries that actually get
executed,so that a   huge function that is called only once in a backend, erroring   out  at  the  first  IF, will not
parsemost of it's queries.   This is surely  a  win  for  performance,  but  it  makes  it   difficult  to  develop.
This will  change  a  little in the   future, but I do delay those  changes  because  I  think  the   changes when
tuplesets get supported will be huge anyway and   complicating the code now wouldn't help.
 


Jan

--

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