From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
Date: Thu, 14 Feb 2008 18:19:51 -0500
Message-ID: <20670.1203031191@sss.pgh.pa.us> (raw)
In-Reply-To: <47B4C9E4.8090002@ultimeth.com>
References: <47B4C9E4.8090002@ultimeth.com>
"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 functions
Schema | 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=#
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: tgl@sss.pgh.pa.us, postgresql@ultimeth.com
Subject: Re: FUNCTIONs and CASTs
In-Reply-To: <20670.1203031191@sss.pgh.pa.us>
* 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