Re: FUNCTIONs and CASTs - Mailing list pgsql-sql

Dean Gibson (DB Administrator) wrote:
> 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.
> 
> 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:

I get the same result: "works here".

richardh=> CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE TABLE

richardh=> INSERT INTO zzz SELECT generate_series(1,100000)::text;
INSERT 0 100000

richardh=> CREATE INDEX zzz_aaa_idx ON zzz (aaa);
CREATE INDEX

richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS 
zzz.aaa%TYPE
richardh->    LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$;
NOTICE:  type reference zzz.aaa%TYPE converted to character
NOTICE:  type reference zzz.aaa%TYPE converted to character
CREATE FUNCTION
Time: 15.268 ms
richardh=> SELECT dummy2('99999');   dummy2
------------ 99999
(1 row)

Time: 1.962 ms
richardh=> DROP INDEX zzz_aaa_idx;
DROP INDEX

richardh=> SELECT dummy2('99999');   dummy2
------------ 99999
(1 row)

Time: 45.418 ms


What does this do on your machine?

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

Hang on though - this function isn't using %TYPE, it's using explicit 
type definitions. If this function is slow, how can it be anything do 
with %TYPE ?

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

Shouldn't be necessary (see above).

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

It specifies the type of the variable (or parameter) in question. The 
reason you can't use %TYPE directly in your SQL is because afaik it's 
not SQL - it's a PostgreSQL extension designed to specify variable types 
in functions. SQL constructs tend to expect a literal type name.

I'm not sure what your problem is, but it's not the %TYPE operator, 
that's clear.

Can you try reproducing the function as a prepared query? That way you 
can run EXPLAIN ANALYSE on it and see what's actually happening here.

--   Richard Huxton  Archonet Ltd