From postgresql@ultimeth.com Thu Feb  14 23:08:26 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 1870C2E002E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 19:08:26 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 07593-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 19:08:17 -0400 (AST)
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id A63E72E002C
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 19:08:22 -0400 (AST)
Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net
	[207.155.204.150])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id B737725ED6C
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 15:08:20 -0800 (PST)
Message-ID: <47B4C9E4.8090002@ultimeth.com>
Date: Thu, 14 Feb 2008 15:08:20 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: FUNCTIONs and CASTs
Content-Type: multipart/alternative;
	boundary="------------050903080208010505060906"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/107
X-Sequence-Number: 30198

This is a multi-part message in MIME format.
--------------050903080208010505060906
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Last night I tore my hair out for about three hours with the following 
problem (v8.3.0):

I had a simple scalar query that I wanted to create a function for.  
However, when I created the function and called it from another query OR 
the PSQL command line, it was two orders of magnitude SLOWER than when I 
manually substituted the parameters into the query and ran it directly.  
Then I figured out what the cause was:

The manual query was doing an indexed column lookup on the value, a 
simple text string.  However, when the function was passed the text 
string as the value, it was comparing the TEXT type of the parameter to 
the CHAR( n) type of the indexed column, and apparently rewriting the 
comparison using a cast of the indexed column.  Needless to say, the 
does not result in an indexed access (probably the index is searched 
sequentially for a match).

I solved the problem by explicitly casting the function parameter to the 
type of the index, and that solved the problem.

So, is this the best (or only) way to solve this?  I haven't done 
exhaustive checking, but it appears that specifying the type of 
parameters in the function prototype is only used for type-checking (and 
function matching), and no conversion between types is done.  Given 
that, I'm not sure of the value of the /tablename/./columnname/%TYPE 
notation, especially since apparently it can only be used in the 
function prototype and not in the body of the function.

If I am wrong on any of the above, I would be pleased to know it.

-- Dean

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


--------------050903080208010505060906
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
Last night I tore my hair out for about three hours with the following
problem (v8.3.0):<br>
<br>
I had a simple scalar query that I wanted to create a function for.&nbsp;
However, when I created the function and called it from another query
OR the PSQL command line, it was two orders of magnitude SLOWER than
when I manually substituted the parameters into the query and ran it
directly.&nbsp; Then I figured out what the cause was:<br>
<br>
The manual query was doing an indexed column lookup on the value, a
simple text string.&nbsp; However, when the function was passed the text
string as the value, it was comparing the TEXT type of the parameter to
the CHAR( n) type of the indexed column, and apparently rewriting the
comparison using a cast of the indexed column.&nbsp; Needless to say, the
does not result in an indexed access (probably the index is searched
sequentially for a match).<br>
<br>
I solved the problem by explicitly casting the function parameter to
the type of the index, and that solved the problem.<br>
<br>
So, is this the best (or only) way to solve this?&nbsp; I haven't done
exhaustive checking, but it appears that specifying the type of
parameters in the function prototype is only used for type-checking
(and function matching), and no conversion between types is done.&nbsp;
Given that, I'm not sure of the value of the <tt class="LITERAL"><tt
 class="REPLACEABLE"><i>tablename</i></tt>.<tt class="REPLACEABLE"><i>columnname</i></tt>%TYPE</tt>
notation, especially since apparently it can only be used in the
function prototype and not in the body of the function.<br>
<br>
If I am wrong on any of the above, I would be pleased to know it.<br>
<br>
-- Dean<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------050903080208010505060906--



From tgl@sss.pgh.pa.us Thu Feb  14 23:19:55 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id EAAD32E002E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 19:19:55 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 67125-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 19:19:52 -0400 (AST)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 4E9D22E002C
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 19:19:52 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1ENJpVM020671;
	Thu, 14 Feb 2008 18:19:51 -0500 (EST)
To: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs 
In-reply-to: <47B4C9E4.8090002@ultimeth.com> 
References: <47B4C9E4.8090002@ultimeth.com>
Comments: In-reply-to "Dean Gibson (DB Administrator)"
	<postgresql@ultimeth.com>
	message dated "Thu, 14 Feb 2008 15:08:20 -0800"
Date: Thu, 14 Feb 2008 18:19:51 -0500
Message-ID: <20670.1203031191@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/108
X-Sequence-Number: 30199

"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> So, is this the best (or only) way to solve this?  I haven't done 
> exhaustive checking, but it appears that specifying the type of 
> parameters in the function prototype is only used for type-checking (and 
> function matching), and no conversion between types is done.

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.

			regards, tom lane


regression=# create table tt(f1 char(10) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tt_f1_key" for table "tt"
CREATE TABLE
regression=# insert into tt select x from generate_series(1,100000) x;
INSERT 0 100000
regression=# \timing
Timing is on.
regression=# select * from tt where f1 = '12345';
     f1     
------------
 12345     
(1 row)

Time: 47.589 ms
regression=# set enable_indexscan TO 0;
SET
Time: 3.146 ms
regression=# set enable_bitmapscan TO 0;
SET
Time: 1.583 ms
regression=# select * from tt where f1 = '12345';
     f1     
------------
 12345     
(1 row)

Time: 414.585 ms
regression=# select * from tt where f1 = '12345';
     f1     
------------
 12345     
(1 row)

Time: 412.167 ms
regression=# reset enable_indexscan;
RESET
Time: 3.037 ms
regression=# select * from tt where f1 = '12345';
     f1     
------------
 12345     
(1 row)

Time: 4.019 ms
regression=# create function foo (tt.f1%type) returns char(10) as $$
declare r tt.f1%type;
begin 
  select f1 into r from tt where f1 = $1;
  return r;
end$$ language plpgsql;
NOTICE:  type reference tt.f1%TYPE converted to character
CREATE FUNCTION
Time: 8.193 ms
regression=# \df foo
                   List of functions
 Schema | Name | Result data type | Argument data types 
--------+------+------------------+---------------------
 public | foo  | character        | character
(1 row)

regression=# select foo('12345'::text);
    foo     
------------
 12345     
(1 row)

Time: 21.683 ms
regression=# select foo('12345'::text);
    foo     
------------
 12345     
(1 row)

Time: 4.098 ms
regression=#  



From postgresql@ultimeth.com Fri Feb  15 01:47:48 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 766392E0043
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 21:47:48 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 81637-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 21:47:37 -0400 (AST)
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 51DED2E0032
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 21:47:45 -0400 (AST)
Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net
	[207.155.204.150])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 35D652FE802
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 17:47:43 -0800 (PST)
Message-ID: <47B4EF3E.4090608@ultimeth.com>
Date: Thu, 14 Feb 2008 17:47:42 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
In-Reply-To: <20670.1203031191@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/109
X-Sequence-Number: 30200

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.
>
> 			regards, tom lane
>   

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:

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

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.

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



From dev@archonet.com Fri Feb  15 10:03:09 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 73F652E003E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 06:03:09 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 54443-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 06:03:02 -0400 (AST)
Received: from pih-relay08.plus.net (pih-relay08.plus.net [212.159.14.134])
	by postgresql.org (Postfix) with ESMTP id 02F9D2E02AB
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 05:38:44 -0400 (AST)
Received: from [84.51.143.99] (helo=server3.office.archonet.com)
	by pih-relay08.plus.net with esmtp (Exim) id 1JPx1l-00036j-Dw;
	Fri, 15 Feb 2008 09:38:41 +0000
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 302E327405A;
	Fri, 15 Feb 2008 09:38:41 +0000 (GMT)
Message-ID: <47B55DA1.1060706@archonet.com>
Date: Fri, 15 Feb 2008 09:38:41 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
CC: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
	<47B4EF3E.4090608@ultimeth.com>
In-Reply-To: <47B4EF3E.4090608@ultimeth.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Plusnet-Relay: b17e8f730cd13b174553a1d265e58b10
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/116
X-Sequence-Number: 30207

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



From postgresql@ultimeth.com Fri Feb  15 18:18:39 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 5885F2E002F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 14:18:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 18982-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 14:18:35 -0400 (AST)
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 32BAF2E002D
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 14:18:35 -0400 (AST)
Received: from [172.24.2.1] (pool-71-113-24-134.sttlwa.dsl-w.verizon.net
	[71.113.24.134])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 805C725ED6C
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 10:18:33 -0800 (PST)
Message-ID: <47B5D790.4090509@ultimeth.com>
Date: Fri, 15 Feb 2008 10:18:56 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
	<47B4EF3E.4090608@ultimeth.com> <47B55DA1.1060706@archonet.com>
In-Reply-To: <47B55DA1.1060706@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/131
X-Sequence-Number: 30222

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.




From tgl@sss.pgh.pa.us Fri Feb  15 22:32:30 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 91B552E002C
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 18:32:30 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 15676-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 18:32:26 -0400 (AST)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id D3AA62E002D
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 18:32:26 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1FMWPtP000656;
	Fri, 15 Feb 2008 17:32:25 -0500 (EST)
To: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs 
In-reply-to: <47B5D790.4090509@ultimeth.com> 
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
	<47B4EF3E.4090608@ultimeth.com> <47B55DA1.1060706@archonet.com>
	<47B5D790.4090509@ultimeth.com>
Comments: In-reply-to "Dean Gibson (DB Administrator)"
	<postgresql@ultimeth.com>
	message dated "Fri, 15 Feb 2008 10:18:56 -0800"
Date: Fri, 15 Feb 2008 17:32:25 -0500
Message-ID: <655.1203114745@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/134
X-Sequence-Number: 30225

"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> 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.

The reason for the lack of communication is that no one else believes
that premise.  Casting a value to the same type it already has is
demonstrably a no-op.

			regards, tom lane



From postgresql@ultimeth.com Fri Feb  15 23:03:08 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 9B3462E002F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 19:03:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 28813-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 19:03:05 -0400 (AST)
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 009132E002E
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 19:03:04 -0400 (AST)
Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net
	[207.155.204.150])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 1ABC525ED6C
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 15:03:03 -0800 (PST)
Message-ID: <47B61A26.4060806@ultimeth.com>
Date: Fri, 15 Feb 2008 15:03:02 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
	<47B4EF3E.4090608@ultimeth.com> <47B55DA1.1060706@archonet.com>
	<47B5D790.4090509@ultimeth.com> <655.1203114745@sss.pgh.pa.us>
In-Reply-To: <655.1203114745@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------010008070805090605080101"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/135
X-Sequence-Number: 30226

This is a multi-part message in MIME format.
--------------010008070805090605080101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2008-02-15 14:32, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
>   
>> 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.
>>     
>
> The reason for the lack of communication is that no one else believes
> that premise.  Casting a value to the same type it already has is
> demonstrably a no-op.
>   
Casing a TEXT item to a CHAR( 9 ) item isn't a no-op.  I've seen this 
before in "EXPLAIN ..." output, where a search on an indexed column will 
be sequential because the planner treats the search value as TEXT rather 
than CHAR( 9 ).

Are you saying that no one believes there is a performance difference?  
Amazing ...

Tom, I've privately eMailed you access instructions to one of my DB 
servers, so you can see for yourself.

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


--------------010008070805090605080101
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-02-15 14:32, Tom Lane wrote:
<blockquote cite="mid:655.1203114745@sss.pgh.pa.us" type="cite">
  <pre wrap="">"Dean Gibson (DB Administrator)" <a class="moz-txt-link-rfc2396E" href="mailto:postgresql@ultimeth.com">&lt;postgresql@ultimeth.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
The reason for the lack of communication is that no one else believes
that premise.  Casting a value to the same type it already has is
demonstrably a no-op.
  </pre>
</blockquote>
Casing a TEXT item to a CHAR( 9 ) item isn't a no-op.&nbsp; I've seen this
before in "EXPLAIN ..." output, where a search on an indexed column
will be sequential because the planner treats the search value as TEXT
rather than CHAR( 9 ).<br>
<br>
Are you saying that no one believes there is a performance difference?&nbsp;
Amazing ...<br>
<br>
Tom, I've privately eMailed you access instructions to one of my DB
servers, so you can see for yourself.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------010008070805090605080101--



From postgresql@ultimeth.com Sat Feb  16 00:46:10 2008
Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 80E402E002D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 20:46:10 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 50247-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 15 Feb 2008 20:45:55 -0400 (AST)
Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14])
	by postgresql.org (Postfix) with ESMTP id 8FED32E002C
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 20:46:01 -0400 (AST)
Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net
	[207.155.204.150])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.mailpen.net (Postfix) with ESMTP id 9A63425ED6C
	for <pgsql-sql@postgresql.org>; Fri, 15 Feb 2008 16:45:59 -0800 (PST)
Message-ID: <47B63246.9070104@ultimeth.com>
Date: Fri, 15 Feb 2008 16:45:58 -0800
From: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Organization: UltiMeth Systems
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: FUNCTIONs and CASTs
References: <47B4C9E4.8090002@ultimeth.com> <20670.1203031191@sss.pgh.pa.us>
	<47B4EF3E.4090608@ultimeth.com> <47B55DA1.1060706@archonet.com>
	<47B5D790.4090509@ultimeth.com> <655.1203114745@sss.pgh.pa.us>
	<47B61A26.4060806@ultimeth.com>
In-Reply-To: <47B61A26.4060806@ultimeth.com>
Content-Type: multipart/alternative;
	boundary="------------020608070708010901010806"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/136
X-Sequence-Number: 30227

This is a multi-part message in MIME format.
--------------020608070708010901010806
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote:
> On 2008-02-15 14:32, Tom Lane wrote:
>
> Casing a TEXT item to a CHAR( 9 ) item isn't a no-op.  I've seen this 
> before in "EXPLAIN ..." output, where a search on an indexed column 
> will be sequential because the planner treats the search value as TEXT 
> rather than CHAR( 9 ).
>
> Are you saying that no one believes there is a performance 
> difference?  Amazing ...
>
> Tom, I've privately eMailed you access instructions to one of my DB 
> servers, so you can see for yourself.
>

OK, it must have been late at 2am when I last ran the tests, as it now 
seems to work.  By "work", I mean that the casting in the function body 
is (in the particular case I was having an issue with) apparently 
unnecessary if the types are proper (which includes the 
table.column%TYPE notation).

I'm happy to find that out, since now I can use the table.column%TYPE 
notation to advantage.

What helped confuse me is that the following function apparently DOES 
need an internal cast:

CREATE OR REPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE 
SQL AS 'SELECT $1';

SELECT zzz( 'abc' );

returns "abc", not "a".  Apparently declarations of CHAR(n) are treated 
as BPCHAR in function prototypes???

-- Dean

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


--------------020608070708010901010806
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote:
<blockquote cite="mid:47B61A26.4060806@ultimeth.com" type="cite">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
On 2008-02-15 14:32, Tom Lane wrote:<br>
  <br>
Casing a TEXT item to a CHAR( 9 ) item isn't a no-op.&nbsp; I've seen this
before in "EXPLAIN ..." output, where a search on an indexed column
will be sequential because the planner treats the search value as TEXT
rather than CHAR( 9 ).<br>
  <br>
Are you saying that no one believes there is a performance difference?&nbsp;
Amazing ...<br>
  <br>
  <strike>Tom, I've privately eMailed you access instructions to one of
my DB
servers, so you can see for yourself.</strike><br>
  <br>
</blockquote>
<br>
OK, it must have been late at 2am when I last ran the tests, as it now
seems to work.&nbsp; By "work", I mean that the casting in the function body
is (in the particular case I was having an issue with) apparently
unnecessary if the types are proper (which includes the
table.column%TYPE notation).<br>
<br>
I'm happy to find that out, since now I can use the table.column%TYPE
notation to advantage.<br>
<br>
What helped confuse me is that the following function apparently DOES
need an internal cast:<br>
<br>
CREATE OR REPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE
SQL AS 'SELECT $1';<br>
<br>
SELECT zzz( 'abc' );<br>
<br>
returns "abc", not "a".&nbsp; Apparently declarations of CHAR(n) are treated
as BPCHAR in function prototypes???<br>
<br>
-- Dean<br>
<pre class="moz-signature" cols="72">-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>
</body>
</html>

--------------020608070708010901010806--