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