By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,167 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

how to append the string to SQL queries in stored procedure of Postgres....

P: 10
i want to append the string which is present in the stored pocedure to SQL query

CREATE OR REPLACE FUNCTION sample(fid varchar,ioffset int)
RETURNS SETOF totaltype AS $$
DECLARE

s totaltype %ROWTYPE;


SQL_Tx_Type VARCHAR(4000);
SQL_User_Count VARCHAR(4000);

BEGIN

tempapp:='a.key==b.key';

for s in

select * from tbl_text1 a,tbl_test2 b where a.id==b.id and (want to append string tempapp here);

loop

return next s;
end loop;

end
$$ LANGUAGE 'plpgsql';


can you help me regarding this..............its urgent

Regards
Shree
Dec 11 '07 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 700
i want to append the string which is present in the stored pocedure to SQL query

CREATE OR REPLACE FUNCTION sample(fid varchar,ioffset int)
RETURNS SETOF totaltype AS $$
DECLARE

s totaltype %ROWTYPE;


SQL_Tx_Type VARCHAR(4000);
SQL_User_Count VARCHAR(4000);

BEGIN

tempapp:='a.key==b.key';

for s in

select * from tbl_text1 a,tbl_test2 b where a.id==b.id and (want to append string tempapp here);

loop

return next s;
end loop;

end
$$ LANGUAGE 'plpgsql';


can you help me regarding this..............its urgent

Regards
Shree
Do it with execute

EXECUTE 'select * from tbl_text1 a,tbl_test2 b where a.id==b.id and '||tempapp;
I'm not sure if it works without EXECUTE
Dec 11 '07 #2

P: 10
Do it with execute

EXECUTE 'select * from tbl_text1 a,tbl_test2 b where a.id==b.id and '||tempapp;
I'm not sure if it works without EXECUTE

i didnt get result...but i have found an alternative way.....i have taken remaining query in a string variable and appended tht variable as u told...now its working...thanks.....

i have got another problem....want to take first parameter in a string variable and append that string in the execute query....its like follow---------

CREATE OR REPLACE FUNCTION sample(fid varchar,ioffset int)
RETURNS SETOF totaltype AS $$
DECLARE

s totaltype %ROWTYPE;


SQL_Tx_Type VARCHAR(4000);
SQL_User_Count VARCHAR(4000);

BEGIN

tempapp:='a.key==$1'; (its showing error here)
squery:='select * from tbl_text1 a,tbl_test2 b where '

for s in

execute squery || tempapp

loop

return next s;
end loop;

end
$$ LANGUAGE 'plpgsql';

$1 value is not taking inside execute statement....can u help me out regarding this problem...its urgent
Regards
Shree
Dec 12 '07 #3

Expert 100+
P: 700
i didnt get result...but i have found an alternative way.....i have taken remaining query in a string variable and appended tht variable as u told...now its working...thanks.....

i have got another problem....want to take first parameter in a string variable and append that string in the execute query....its like follow---------

CREATE OR REPLACE FUNCTION sample(fid varchar,ioffset int)
RETURNS SETOF totaltype AS $$
DECLARE

s totaltype %ROWTYPE;


SQL_Tx_Type VARCHAR(4000);
SQL_User_Count VARCHAR(4000);

BEGIN

tempapp:='a.key==$1'; (its showing error here)
squery:='select * from tbl_text1 a,tbl_test2 b where '

for s in

execute squery || tempapp

loop

return next s;
end loop;

end
$$ LANGUAGE 'plpgsql';

$1 value is not taking inside execute statement....can u help me out regarding this problem...its urgent
Regards
Shree
maybe like this
tempapp:='a.key=='||$1
doesn't it work ?
If no what is the error message
Dec 12 '07 #4

P: 10
maybe like this
tempapp:='a.key=='||$1
doesn't it work ?
If no what is the error message

hey,thanks....i have got the result..... :)


got one more problem------------------

i have declared a string variable with some value,i want to check whether that string ends with "and" ....

how to do it in stored procedure----its like this

CREATE OR REPLACE FUNCTION test()
RETURNS varchar AS $$


DECLARE
samp varchar(400)
wr varchar;
index1 int;
begin
wr:='';
samp:=' (select * from tbl_usertable where a.ecpclaim_carrier_id ILIKE
''PAPER'' and '

IF (samp.endsWith(''and'')) then

index1:=samp.lastIndexOf(''and'');
samp:=samp.replace(index,length(samp),'''');
end if;

IF (samp IS NULL) then
samp:=samp || wr;
end if;

return
end
$$ LANGUAGE 'plpgsql';

its showing error in "endsWith" and "lastIndexOf", so what are the functions we have to use in stored procedure of postgre for the functions "endsWith" and "lastIndexOf"???????

help me out plz.........

Regards
Shree
Dec 13 '07 #5

Expert 100+
P: 700
hey,thanks....i have got the result..... :)


got one more problem------------------

i have declared a string variable with some value,i want to check whether that string ends with "and" ....

how to do it in stored procedure----its like this

CREATE OR REPLACE FUNCTION test()
RETURNS varchar AS $$


DECLARE
samp varchar(400)
wr varchar;
index1 int;
begin
wr:='';
samp:=' (select * from tbl_usertable where a.ecpclaim_carrier_id ILIKE
''PAPER'' and '

IF (samp.endsWith(''and'')) then

index1:=samp.lastIndexOf(''and'');
samp:=samp.replace(index,length(samp),'''');
end if;

IF (samp IS NULL) then
samp:=samp || wr;
end if;

return
end
$$ LANGUAGE 'plpgsql';

its showing error in "endsWith" and "lastIndexOf", so what are the functions we have to use in stored procedure of postgre for the functions "endsWith" and "lastIndexOf"???????

help me out plz.........

Regards
Shree
I think position() function might help, but for more info see here
http://www.postgresql.org/docs/8.2/i...ns-string.html
Dec 13 '07 #6

P: 10
I think position() function might help, but for more info see here
http://www.postgresql.org/docs/8.2/i...ns-string.html

ya,got the result..thanks a lot.......

got one more issue.....

how to check case for a string in stored procedure....

i want to use equalsIgnoreCase().....i thinks this string function is not available in postgreSQL....so how can i check case for string here......give me the syntax plz

Regards
Shree
Dec 14 '07 #7

Expert 100+
P: 700
ya,got the result..thanks a lot.......

got one more issue.....

how to check case for a string in stored procedure....

i want to use equalsIgnoreCase().....i thinks this string function is not available in postgreSQL....so how can i check case for string here......give me the syntax plz

Regards
Shree
I'm not suer if understand whatvyou want, but if you want to compare strings without case senstitive ins SQL queries you can use ILIKE operator, or you can user upper() or lower() functions.
Dec 14 '07 #8

P: 10
I'm not suer if understand whatvyou want, but if you want to compare strings without case senstitive ins SQL queries you can use ILIKE operator, or you can user upper() or lower() functions.

Acually,i want to check the condition like this.......

if((sReceiverType IS NOT NULL) AND (sReceiverType='paper')) then

-----------some statement here---------

end if;


if input of "sReceiverType" is 'PAPER' then this condition will be false...

so,i want to ingnore case.......

which is like this in java class-------
sReceiverType.equalsIgnoreCase("paper");

so,what string function we have to use to do the same.....???????


thanks and Regards
Shree
Dec 15 '07 #9

Expert 100+
P: 700
Acually,i want to check the condition like this.......

if((sReceiverType IS NOT NULL) AND (sReceiverType='paper')) then

-----------some statement here---------

end if;


if input of "sReceiverType" is 'PAPER' then this condition will be false...

so,i want to ingnore case.......

which is like this in java class-------
sReceiverType.equalsIgnoreCase("paper");

so,what string function we have to use to do the same.....???????


thanks and Regards
Shree
Maybe like this (it's the simples solution)
if((sReceiverType IS NOT NULL) AND (lower(sReceiverType)='paper')) then
Dec 15 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.