"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> So, is this the best (or only) way to solve this? I haven't done
> exhaustive checking, but it appears that specifying the type of
> parameters in the function prototype is only used for type-checking (and
> function matching), and no conversion between types is done.
It's not exactly clear what you checked, but it works as expected for
me. See test case below, proving that indexscan works just fine with
a parameter declared using %type.
regards, tom lane
regression=# create table tt(f1 char(10) unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tt_f1_key" for table "tt"
CREATE TABLE
regression=# insert into tt select x from generate_series(1,100000) x;
INSERT 0 100000
regression=# \timing
Timing is on.
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 47.589 ms
regression=# set enable_indexscan TO 0;
SET
Time: 3.146 ms
regression=# set enable_bitmapscan TO 0;
SET
Time: 1.583 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 414.585 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 412.167 ms
regression=# reset enable_indexscan;
RESET
Time: 3.037 ms
regression=# select * from tt where f1 = '12345'; f1
------------12345
(1 row)
Time: 4.019 ms
regression=# create function foo (tt.f1%type) returns char(10) as $$
declare r tt.f1%type;
begin select f1 into r from tt where f1 = $1; return r;
end$$ language plpgsql;
NOTICE: type reference tt.f1%TYPE converted to character
CREATE FUNCTION
Time: 8.193 ms
regression=# \df foo List of functionsSchema | Name | Result data type | Argument data types
--------+------+------------------+---------------------public | foo | character | character
(1 row)
regression=# select foo('12345'::text); foo
------------12345
(1 row)
Time: 21.683 ms
regression=# select foo('12345'::text); foo
------------12345
(1 row)
Time: 4.098 ms
regression=#
