Re: FUNCTIONs and CASTs

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".