Re: FUNCTIONs and CASTs

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