From: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
Date: Fri, 15 Feb 2008 10:18:56 -0800
Message-ID: <47B5D790.4090509@ultimeth.com> (raw)
In-Reply-To: <47B55DA1.1060706@archonet.com>
References: <47B4C9E4.8090002@ultimeth.com>
<20670.1203031191@sss.pgh.pa.us>
<47B4EF3E.4090608@ultimeth.com>
<47B55DA1.1060706@archonet.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@ultimeth.com
Subject: Re: FUNCTIONs and CASTs
In-Reply-To: <47B5D790.4090509@ultimeth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox