Skip site navigation (1) Skip section navigation (2)

proposal: early casting in plpgsql

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: early casting in plpgsql
Date: 2009-05-28 17:42:14
Message-ID: 162867790905281042p13cd2bb7pfbecb662f29ba167@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello

current plpgsql cannot detect early some errors based on unknown
casting. Other problem is IO casting.

The reason is an late casting:

current_code is some like:

val = eval_expr(query, &result_type);
if (result_type != expected_type)
{
   str = convert_to_string(val, result_type);
   val = convert_from_string(val, expected_type);
}

I propose for types with typmod -1 early casting - etc casting to
target type on planner level. We cannot use this method for defined
typmod, because we would to raise exception for following situation:

varchar(3) := 'ABCDE'; - casting do quietly necessary truncation

This should be everywhere, where we know an target type.

What this needs?

* new SPI function SPI_prepare_function_with_target_types, that calls
coerce_to_target_type function.
* add new field to PLpgSQL_expr - Oid *target_type

benefits:
* possible some strict mode - that use only predefined cast functions
(without I/O general conversion)
* some minor speed
* fix some strange issues
http://archives.postgresql.org/pgsql-hackers/2008-12/msg01932.php
* consistent behave with SQL

postgres=# create function fot(i numeric) returns date as $$begin
return i;end; $$ language plpgsql;
CREATE FUNCTION
Time: 2,346 ms
postgres=# select extract (year from fot(20081010));
CONTEXT:  PL/pgSQL function "fot" line 1 at RETURN
 date_part
-----------
      2008
(1 row)

what is nonsense
postgres=# select extract(year from 20081010::numeric::date);
ERROR:  cannot cast type numeric to date
LINE 1: select extract(year from 20081010::numeric::date);
                                                  ^
Issues:
* current casting functions doesn't raise exception when we lost some detail :(

postgres=# select 'abc'::varchar(2), 10.22::numeric(10,1), 10.22::integer;
 varchar | numeric | int4
---------+---------+------
 ab      |    10.2 |   10
(1 row)


* current integer input functions are too simple:

ERROR:  invalid input syntax for integer: "10.00"
LINE 1: select int '10.00';
                   ^
Possible enhancing:
when target variable has attypmod, then we could add to plan IO
casting via some new functions - this should simplify plpgsql code -
any casting should be removed

Ideas, comments?

regards
Pavel Stehule

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-05-28 17:54:03
Subject: Re: PostgreSQL Developer meeting minutes up
Previous:From: Alvaro HerreraDate: 2009-05-28 17:40:25
Subject: Re: PostgreSQL Developer meeting minutes up

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group