Re: short-cutting if sum()>constant - Mailing list pgsql-sql

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 = 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*.

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
>