Date: Wed, 23 Dec 2009 18:43:18 +0100
From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: Re: short-cutting if sum()>constant
Message-ID: <20091223184318.5fe9b4d8@dawn.webthatworks.it>
In-Reply-To: <28619.1261586191@sss.pgh.pa.us>
References: <20091222133527.3007bd96@dawn.webthatworks.it>
	<162867790912221147r7e3041fej44332a05faf915d6@mail.gmail.com>
	<20091223000031.54ad09b5@dawn.webthatworks.it>
	<20091223010940.51c5c069@dawn.webthatworks.it>
	<20091223024635.48d66554@dawn.webthatworks.it>
	<162867790912222338v73261b73u5c4e88a5ce85918f@mail.gmail.com>
	<20091223115628.1709caf5@dawn.webthatworks.it>
	<162867790912230352v5b6e395fm1880111c04eaa626@mail.gmail.com>
	<4B323F21.3020007@postnewspapers.com.au>
	<28619.1261586191@sss.pgh.pa.us>
Organization: http://www.webthatworks.it
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Wed, 23 Dec 2009 11:36:31 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Craig Ringer <craig@postnewspapers.com.au> writes:
> > Pavel Stehule wrote:
> >> these queries are executed in some special mode, but still it
> >> is more expensive than C a = a + 1
> 
> > ... and may have different rules, so you can't just write a
> > simple "map expressions to C equivalents" arithmetic evaluator.

> Yeah.  As an example, overflow is supposed to be caught in "a + 1",
> unlike what would happen in C.

> In principle you could map some of the builtin operators into
> inline code, but it would be a great deal of work and the results
> would be un-portable.

Tank you all for being so patient...
I really miss how actually procedural languages works internally.

doesn't pg routinely map between SQL and C?

What is the difference between

select a+a from data;
and
a := a + a;
in a plpgsql function?

plpgsql knows that a are eg. int so it could just use the same C
code that it uses when it has to sum a+a in sql.

My guess since I don't even know what to look for to get an idea of
the internal working of plpgsql is that the interpreter translate
the code into SQL (sort of...), it sends it to the parser through
SPI_execute/prepare etc... (so yeah maybe for the "data" it is not
really sending "text" representation of data) but still... the
"code" has to be further interpreted...

So something like:
a := a + a;
turns out to be:
SPI_prepare("SELECT $1 + $2", 2, ...);
and this is going to be called for every loop.
while I thought the SQL engine and plpgsql interpreter were nearer
so that the interpreter could push directly in the SQL engine the
values of a.

Am I getting nearer?

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it