Received: from jupiter.jw.home (A5f53.pppool.de [213.6.95.83]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAHDrks42720 for ; Fri, 17 Nov 2000 08:53:46 -0500 (EST) (envelope-from janwieck@Yahoo.com) Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id FAA01573; Wed, 15 Nov 2000 05:20:41 -0500 From: Jan Wieck Message-Id: <200011151020.FAA01573@jupiter.jw.home> Subject: Re: Re: Requests for Development In-Reply-To: <3A0BC752.nail1EE1D4VAA@ed.ed> from KuroiNeko at "Nov 10, 2000 10:00:50 am" To: KuroiNeko Date: Wed, 15 Nov 2000 05:20:39 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200011/133 X-Sequence-Number: 164 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 first call. Since version 8.0 Tcl uses a bytecode compiler and will not interpret the real source text again and again. 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 parse most 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 tuple sets 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 #