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

[Shree111]

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

[rski]

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

[Shree111]

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

[rski]

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

[Shree111]

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

[rski]

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

[Shree111]

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

[rski]

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.

[Shree111]

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

[rski]

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