From skarthi98@hotmail.com Sun Apr  8 19:47:52 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 3CF2C9FA616;
	Sun,  8 Apr 2007 16:47:52 -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 32783-06; Sun,  8 Apr 2007 16:47:42 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from bay0-omc3-s39.bay0.hotmail.com (bay0-omc3-s39.bay0.hotmail.com
	[65.54.246.239])
	by postgresql.org (Postfix) with ESMTP id DE7AC9FA602;
	Sun,  8 Apr 2007 16:47:43 -0300 (ADT)
Received: from BAY131-W13 ([65.55.136.48]) by bay0-omc3-s39.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.2668);
	Sun, 8 Apr 2007 12:47:42 -0700
Message-ID: <BAY131-W13C024D09AE528E6496EECB05A0@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_0a4b63f4-f27f-4bdb-8e5b-7e75388317cb_"
X-Originating-IP: [69.181.160.236]
From: Karthikeyan Sundaram <skarthi98@hotmail.com>
To: postgres admin <pgsql-admin@postgresql.org>, postgres sql
	<pgsql-sql@postgresql.org>
Subject: Question on pgpsql function
Date: Sun, 8 Apr 2007 12:47:42 -0700
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 08 Apr 2007 19:47:42.0720 (UTC)
	FILETIME=[C5A5E000:01C77A16]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/71
X-Sequence-Number: 25135

--_0a4b63f4-f27f-4bdb-8e5b-7e75388317cb_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


Hi Everybody,
=20
   I am using Postgres 8.1.0 and I have a requirement.
=20
 I have a table=20
=20
  create table weekly_tbl (id int, week_flag bit(7) not null default '11111=
11');
=20
I want to create a function like this
=20
create function week_func (int) returns weekly_tbl as=20
$$
  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=3D$1;
$$
language SQL;
=20
 I am getting an error message=20
ERROR:  function substr(bit, integer, integer) does not existHINT:  No func=
tion matches the given name and argument types. You may need to add explici=
t type casts.CONTEXT:  SQL function "week_func"
=20
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?
=20
Regards
skarthi
=20
_________________________________________________________________
It=92s tax season, make sure to follow these few simple tips=20
http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.=
aspx?icid=3DWLMartagline=

--_0a4b63f4-f27f-4bdb-8e5b-7e75388317cb_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
P
{
margin:0px;
padding:0px
}
body
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body>
Hi Everybody,<BR>
&nbsp;<BR>
&nbsp;&nbsp; I am using Postgres 8.1.0 and I have a requirement.<BR>
&nbsp;<BR>
&nbsp;I have a table <BR>
&nbsp;<BR>
&nbsp; create table weekly_tbl (id int, week_flag bit(7) not null default '=
1111111');<BR>
&nbsp;<BR>
I want to create a function like this<BR>
&nbsp;<BR>
create function week_func (int) returns weekly_tbl as <BR>
$$<BR>
&nbsp; select id,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr(week_fl=
ag,1,1) as monday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr(week_fl=
ag,2,1) as tuesday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr(week_fl=
ag,3,1) as wednesday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr(week_fl=
ag,4,1) as thursday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; substr(week_fl=
ag,5,1) as friday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;substr(we=
ek_flag,6,1) as saturday_flag,<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;substr(we=
ek_flag,7,1) as sunday_flag<BR>
&nbsp;&nbsp;&nbsp; from weekly_tbl where id=3D$1;<BR>
$$<BR>
language SQL;<BR>
&nbsp;<BR>
&nbsp;I am getting an error message <BR>
<BR>ERROR:&nbsp; function substr(bit, integer, integer) does not exist<BR>H=
INT:&nbsp; No function matches the given name and argument types. You may n=
eed to add explicit type casts.<BR>CONTEXT:&nbsp; SQL function "week_func"<=
BR><BR>
&nbsp;<BR>
I know I can do this in view.&nbsp; But for a purpose, I don't want to do i=
t in view. Can somebody help me?<BR>
&nbsp;<BR>
Regards<BR>
skarthi<BR>
<BR>&nbsp;<BR><br /><hr />It=92s tax season, make sure to follow these few =
simple tips  <a href=3D'http://articles.moneycentral.msn.com/Taxes/Preparat=
ionTips/PreparationTips.aspx?icid=3DWLMartagline' target=3D'_new'>Check it =
out!</a></body>
</html>=

--_0a4b63f4-f27f-4bdb-8e5b-7e75388317cb_--



From aklaver@comcast.net Sun Apr  8 20:22:07 2007
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



From tgl@sss.pgh.pa.us Sun Apr  8 23:20:57 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 4ECA59FB1BD;
	Sun,  8 Apr 2007 20:20:57 -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 14836-06; Sun,  8 Apr 2007 20:20:49 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 0FBBD9FA605;
	Sun,  8 Apr 2007 20:20:49 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l38NKkis011752;
	Sun, 8 Apr 2007 19:20:46 -0400 (EDT)
To: Karthikeyan Sundaram <skarthi98@hotmail.com>
cc: postgres admin <pgsql-admin@postgresql.org>,
	postgres sql <pgsql-sql@postgresql.org>
Subject: Re: Question on pgpsql function 
In-reply-to: <BAY131-W13C024D09AE528E6496EECB05A0@phx.gbl> 
References: <BAY131-W13C024D09AE528E6496EECB05A0@phx.gbl>
Comments: In-reply-to Karthikeyan Sundaram <skarthi98@hotmail.com>
	message dated "Sun, 08 Apr 2007 12:47:42 -0700"
Date: Sun, 08 Apr 2007 19:20:46 -0400
Message-ID: <11751.1176074446@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/73
X-Sequence-Number: 25137

Karthikeyan Sundaram <skarthi98@hotmail.com> writes:
>  I am getting an error message 
> ERROR:  function substr(bit, integer, integer) does not existHINT:  No func=
> tion matches the given name and argument types. You may need to add explici=
> t type casts.CONTEXT:  SQL function "week_func"

I think you want substring() not substr().

			regards, tom lane