Re: FUNCTIONs and CASTs

On 2008-02-15 01:38, Richard Huxton wrote:
> 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 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
You REMOVED the CAST from the function definition. Yes, if you do that,
it works !!!

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

Again, you are not understanding my point. My point was that specifying
tablename.columnname%TYPE notation doesn't help with the performance
problem; I have to explicitly cast the parameter in the body of the
function. Since I have to do that anyway, why use the
tablename.columnname%TYPE notation?

>
>
>> 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.
So? What does that accomplish, over just using "TEXT"?

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

As I said, I don't have a problem with the function; I modified it to
work. My point was, why use the tablename.columnname%TYPE notation when
"TEXT" works just as well (for anything that converts to it)???

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.