From: | "Anoop G" <anoopmadavoor(at)gmail(dot)com> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem in dynamic query execution in plpgsql |
Date: | 2008-07-12 09:15:23 |
Message-ID: | 1a027d210807120215j5a875413kd93cdd814758c0d9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2008-07-12 09:20:37 | Re: Rollback in Postgres |
Previous Message | Dave Page | 2008-07-12 08:40:37 | Re: Rollback in Postgres |