Re: FUNCTIONs and CASTs - Mailing list pgsql-sql

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