Date: Wed, 23 Dec 2009 15:27:28 +0100
From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: Re: short-cutting if sum()>constant
Message-ID: <20091223152728.1e38dd1c@dawn.webthatworks.it>
In-Reply-To: <162867790912230535m6c84a1cfodea1c269d3d84301@mail.gmail.com>
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>
	<20091223140116.1c79d821@dawn.webthatworks.it>
	<162867790912230535m6c84a1cfodea1c269d3d84301@mail.gmail.com>
Organization: http://www.webthatworks.it
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

On Wed, 23 Dec 2009 14:35:27 +0100
Pavel Stehule <pavel.stehule@gmail.com> wrote:


> a) simplicity. PLpgSQL interpret is very simple.
> b) mostly types are little bit different behave than natural C
> types - domains are different, C types doesn't know NULL value, ...

But well... there should be a mapping somewhere between SQL int and
C and it can be plainly reused.

$b =3D 5 + "10";

is going to be far more problematic to execute... but I guess that's
exactly the problem.

create or replace function tano(z int, out b int) returns int as
$$
declare
        x varchar(4) =3D '12';
        y int =3D 10;
begin
        b :=3D x + y;
        return;
end;
$$ language plpgsql;

select * from tano(1);
ERROR:  operator does not exist: character varying + integer
LINE 1: SELECT   $1  +  $2
                     ^

=46rom what I can guess... the interpreter is sending SQL commands to
pg and waiting result back. So actually the "interpreter" isn't
actually aware of data types... it just relies on SQL to spot data
type mismatch.
That means... that everything is converted back and forward to
*text*.
On the other side once a SQL command is interpreted it actually
knows what data types are and can pretty quickly sum int to int just
taking care of nulls and overflows that would be anyway problems for
any other language trying to sum "SQL ints".

Even if it had to sum int and bigint the code could be optimised for
just that and the need of a cast should be known in advance before
every "loop".

Did I get it?

That's what you were trying to make me understand with:

> It little bit nonsense. On 99% plpgsql use SPI api and work with
> variables via query interface. PLpgSQL can do loop statement, if
> statement, but for all others use internal query based API.

> this query is little but different, than you original request, but
> it could work for you.

Yep... making clear a is an int simplify the problem quite a lot.
But you couldn't use generate_series if a was not an int.

thanks

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