From: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
Date: Thu, 14 Feb 2008 17:47:42 -0800
Message-ID: <47B4EF3E.4090608@ultimeth.com> (raw)
In-Reply-To: <20670.1203031191@sss.pgh.pa.us>
References: <47B4C9E4.8090002@ultimeth.com>
<20670.1203031191@sss.pgh.pa.us>
On 2008-02-14 15:19, Tom Lane wrote:
> 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
>
Consider:
CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE
LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) =
aaa';
The notation "zzz.aaa%TYPE" fails when specified in the cast; it's fine
in the function prototype. However, specifying it in the function
prototype doesn't appear to help the performance issue:
Here is the actual function that caused be heartburn. The types in the
function prototype match EXACTLY the types of the actual parameters
being passed (and I also tried it with the tablename.columnname%TYPE
notation), and yet this function is slow. However, if I replace the
"$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function
is very fast. Note that ALL of the column names in the function below
are indexed, so this function should be very fast (and is, with the CASTs).
CREATE OR REPLACE FUNCTION "Functions".prior_call( CHAR( 10 ),
CHAR( 9 ), DATE) RETURNS BOOLEAN
STABLE RETURNS NULL ON NULL INPUT LANGUAGE SQL AS $SQL$
SELECT COALESCE( (SELECT TRUE
FROM lic_hd
NATURAL JOIN lic_en
NATURAL JOIN lic_am
WHERE $1 = licensee_id
AND $2
IN( callsign, prev_callsign )
AND $3
> grant_date
LIMIT 1),
(SELECT TRUE
FROM _preuls
WHERE $1 = licensee_id
AND $2
IN( callsign, prev_callsign )
LIMIT 1),
FALSE )
$SQL$;
So, I think you can see why it would be nice if the
tablename.columnname%TYPE notation could be used in the function body.
I'm not asking for that as an enhancement; rather, I'm trying to
understand what the tablename.columnname%TYPE notation accomplishes,
since specifying it in the function prototype doesn't appear to
accomplish anything (at least for me) over just specifying "TEXT".
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: postgresql@ultimeth.com
Subject: Re: FUNCTIONs and CASTs
In-Reply-To: <47B4EF3E.4090608@ultimeth.com>
* 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