Re: short-cutting if sum()>constant
От | Pavel Stehule |
---|---|
Тема | Re: short-cutting if sum()>constant |
Дата | |
Msg-id | 162867790912230804l51fc3a4fxdf36ea072f091537@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: short-cutting if sum()>constant (Ivan Sergio Borgonovo <mail@webthatworks.it>) |
Список | 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 >
В списке pgsql-sql по дате отправления: