Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 3418B9FA601;
	Sun,  8 Apr 2007 17:22:07 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 12478-05; Sun,  8 Apr 2007 17:21:59 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from rwcrmhc15.comcast.net (rwcrmhc15.comcast.net [216.148.227.155])
	by postgresql.org (Postfix) with ESMTP id 12A309FA41D;
	Sun,  8 Apr 2007 17:22:01 -0300 (ADT)
Received: from [192.168.0.9]
 (c-24-17-161-80.hsd1.wa.comcast.net[24.17.161.80])
	by comcast.net (rwcrmhc15) with ESMTP
	id <20070408202159m15007gjvce>; Sun, 8 Apr 2007 20:21:59 +0000
From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question on pgpsql function
Date: Sun, 8 Apr 2007 13:22:11 -0700
User-Agent: KMail/1.9.1
Cc: Karthikeyan Sundaram <skarthi98@hotmail.com>,
	postgres admin <pgsql-admin@postgresql.org>
References: <BAY131-W13C024D09AE528E6496EECB05A0@phx.gbl>
In-Reply-To: <BAY131-W13C024D09AE528E6496EECB05A0@phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200704081322.11879.aklaver@comcast.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/72
X-Sequence-Number: 25136

On Sunday 08 April 2007 12:47 pm, Karthikeyan Sundaram wrote:
> Hi Everybody,
>
>    I am using Postgres 8.1.0 and I have a requirement.
>
>  I have a table
>
>   create table weekly_tbl (id int, week_flag bit(7) not null default
> '1111111');
>
> I want to create a function like this
>
> create function week_func (int) returns weekly_tbl as
> $$
>   select id,
>            substr(week_flag,1,1) as monday_flag,
>            substr(week_flag,2,1) as tuesday_flag,
>            substr(week_flag,3,1) as wednesday_flag,
>            substr(week_flag,4,1) as thursday_flag,
>            substr(week_flag,5,1) as friday_flag,
>            substr(week_flag,6,1) as saturday_flag,
>            substr(week_flag,7,1) as sunday_flag
>     from weekly_tbl where id=$1;
> $$
> language SQL;
>
>  I am getting an error message
> ERROR:  function substr(bit, integer, integer) does not existHINT:  No
> function matches the given name and argument types. You may need to add
> explicit type casts.CONTEXT:  SQL function "week_func"
>
> I know I can do this in view.  But for a purpose, I don't want to do it in
> view. Can somebody help me?
>
> Regards
> skarthi

The problem is with your use of the substr() function. You are passing it a 
field of type bit (week_flag) where the function needs a text type.  See the 
documentation for bit functions-
http://www.postgresql.org/docs/8.2/interactive/functions-bitstring.html
There are some bit compatible string functions mentioned.

-- 
Adrian Klaver
aklaver@comcast.net