DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=Jiwj61ZvMeXFsu1Ui+Hgxoeg2TtTBDic3fZzphn69qdARkJFvtODc+thDOfFllRKLB
	HpVy+qjDlJTG+SkTACNrzLuzb+Iff5aoxoCo+0iiRubw7GAh5beU3B5XR0N3KOeECigs
	RmZoukLZzGJdjxdgFr0hcPY3PNPi/cLQd8Qsw=
MIME-Version: 1.0
In-Reply-To: <20091223152728.1e38dd1c@dawn.webthatworks.it>
References: <20091222133527.3007bd96@dawn.webthatworks.it>
	<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>
	<20091223152728.1e38dd1c@dawn.webthatworks.it>
Date: Wed, 23 Dec 2009 17:04:37 +0100
Message-ID: <162867790912230804l51fc3a4fxdf36ea072f091537@mail.gmail.com>
Subject: Re: short-cutting if sum()>constant
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Ivan Sergio Borgonovo <mail@webthatworks.it>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2009/12/23 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> 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
> =C2=A0 =C2=A0 =C2=A0 =C2=A0x varchar(4) =3D '12';
> =C2=A0 =C2=A0 =C2=A0 =C2=A0y int =3D 10;
> begin
> =C2=A0 =C2=A0 =C2=A0 =C2=A0b :=3D x + y;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0return;
> end;
> $$ language plpgsql;
>
> select * from tano(1);
> ERROR: =C2=A0operator does not exist: character varying + integer
> LINE 1: SELECT =C2=A0 $1 =C2=A0+ =C2=A0$2
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
>
> From 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*.

no - not everything. plpgsql hold values in PostgreSQL native types.
But these types are not 100% equal to C types. Integer is +- equal to
C int. Varchar is absolutly different then C string.

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

it could be, but it isn't.

PLpgSQL is very simple - you have to know, so every function is
"recompiled" everytime when function is called first time in session.
So there are not time for full optimalisations like C languages.
PLpgSQL do fast non optimalised execution - like Pascal. If you need
well optimized code, then you have to use C language and external
stored procedures. PLpgSQL is best as glue of SQL statements. Not for
numeric calculation, complex string operations.

Regards
Pavel Stehule


>
> 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
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>