Re: FUNCTIONs and CASTs - Mailing list pgsql-sql

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.