Hai Kretschmer , Thanks for your reply, it help to solve my problem. I have few for doubts regarding dynamic query
I have a table structure: Column | Type | Modifiers --------+------------------+----------- mf | double precision | sf | double precision | comm | integer | I create a the following funtion create or replace function test_perc() returns setof record as $body$ declare vchr_query VARCHAR(100); r record; begin --vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; vchr_query:= 'SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; RETURN; end$body$ language 'plpgsql' function created but when I am traing to run this function I got the following error ERROR: syntax error at or near "–" at character 18 QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as flt_claim CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement LINE 1: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as... How i can solve this ? I tried another method to solve this I create a function to calculate the value create or replace function calc_perse(double precision,double precision,double precision) returns double precision as $body$ declare res double precision ; begin res :=( $1 - $1* $3/100 )- ($2 - $2*$3/100); return res; end$body$ language 'plpgsql' Then i try to call it from another function vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc'; FOR r in EXECUTE vchr_query LOOP RETURN NEXT r; END LOOP; It also faild, How I can call a function like this from other function? thanks in advance Anoop G
