Message-ID: <47B4EF3E.4090608@ultimeth.com>
Date: Thu, 14 Feb 2008 17:47:42 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
In-Reply-To: <20670.1203031191@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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