Re: Problem in dynamic query execution in plpgsql - Mailing list pgsql-sql
| From | Anoop G |
|---|---|
| Subject | Re: Problem in dynamic query execution in plpgsql |
| Date | |
| Msg-id | 1a027d210807120215j5a875413kd93cdd814758c0d9@mail.gmail.com Whole thread Raw |
| In response to | Re: Problem in dynamic query execution in plpgsql ("A. Kretschmer" <andreas.kretschmer@schollglas.com>) |
| Responses |
Re: Problem in dynamic query execution in plpgsql
|
| List | pgsql-sql |
Hai <span>Kretschmer</span> ,<br /> Thanks for your reply, it help to solve my problem. I have few for doubts regardingdynamic query<br /><br />I have a table structure:<br /><br /> Column | Type | Modifiers<br />--------+------------------+-----------<br/> mf | double precision |<br /> sf | double precision |<br /> comm | integer |<br /><br />I create a the following funtion <br /><br /><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">create or replace function test_perc() returns setof recordas $body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">declare</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">vchr_queryVARCHAR(100);</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><fontsize="3">r record;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><fontsize="3">begin</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><fontsize="3">--vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) as flt_claim from calc';</font></font><p style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">vchr_query:= 'SELECT mf,sf,(mf – mf* comm /100) – (sf – sf * comm/100) as flt_claim';</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu SerifCondensed, serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">END LOOP;</font></font><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">RETURN;</font></font><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">end$body$<br />language 'plpgsql'</font></font><p style="margin-bottom:0.2in;"><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">functioncreated</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">butwhen I am traing to run this function I got the following error</font></font><p style="margin-bottom: 0.2in;">ERROR: syntax error at or near "–" at character 18<br /> QUERY: SELECT mf,sf,(mf – mf * comm /100) – (sf – sf *comm/100) as flt_claim<br />CONTEXT: PL/pgSQL function "test_perc" line 7 at for over execute statement<br />LINE 1: SELECTmf,sf,(mf – mf * comm /100) – (sf – sf * comm/100) as...<br /><br /><p style="margin-bottom: 0.2in;">How i can solvethis ? <br /><p style="margin-bottom: 0.2in;"><br /><p style="margin-bottom: 0.2in;">I tried another method tosolve this <br /><p style="margin-bottom: 0.2in;"> I create a function to calculate the value<p style="margin-bottom:0.2in;"><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">createor replace function calc_perse(double precision,double precision,double precision) returns double precisionas $body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">declare</font></font><pstyle="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">resdouble precision ;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><fontsize="3">begin</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed, serif"><fontsize="3">res :=( $1 - $1* $3/100 )- ($2 - $2*$3/100); </font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">return res;</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">end$body$</font></font><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">language 'plpgsql'</font></font><br /><p style="margin-bottom: 0.2in;">Theni try to call it from another function<p style="margin-bottom: 0.2in;"><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3">vchr_query:='SELECT mf,sf,comm,calc_perse(mf,sf,comm) asflt_claim from calc';</font></font><p style="margin-bottom: 0.2in;"><p style="margin-bottom: 0.2in;"><font face="DejaVuSerif Condensed, serif"><font size="3">FOR r in EXECUTE vchr_query LOOP</font></font><p style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">RETURN NEXT r;</font></font><p style="margin-bottom:0.2in;"><font face="DejaVu Serif Condensed, serif"><font size="3">END LOOP;</font></font><p><p><p style="margin-bottom:0.2in;">It also faild, How I can call a function like this from other function?<p style="margin-bottom:0.2in;">thanks in advance<p style="margin-bottom: 0.2in;">Anoop G<br /><p style="margin-bottom: 0.2in;"><fontface="DejaVu Serif Condensed, serif"><font size="3"><br /></font></font><p><p style="margin-bottom: 0.2in;"><br/><p style="margin-bottom: 0.2in;"><br /><p style="margin-bottom: 0.2in;"><font face="DejaVu Serif Condensed,serif"><font size="3"><br /></font></font><br /><br /> <br />