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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
