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