Обсуждение: Question on pgpsql function
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 exist
HINT: 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
It’s tax season, make sure to follow these few simple tips Check it out!
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 exist
HINT: 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
It’s tax season, make sure to follow these few simple tips Check it out!
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
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