From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: Re: short-cutting if sum()>constant
Date: Wed, 23 Dec 2009 15:27:28 +0100
Message-ID: <20091223152728.1e38dd1c@dawn.webthatworks.it> (raw)
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>
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 = 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) = '12';
y int = 10;
begin
b := x + y;
return;
end;
$$ language plpgsql;
select * from tano(1);
ERROR: operator does not exist: character varying + integer
LINE 1: SELECT $1 + $2
^
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*.
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
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@webthatworks.it
Subject: Re: short-cutting if sum()>constant
In-Reply-To: <20091223152728.1e38dd1c@dawn.webthatworks.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox