Skip site navigation (1) Skip section navigation (2)

Re: Problem in dynamic query execution in plpgsql

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 (view raw or flat)
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

In response to

Responses

pgsql-sql by date

Next:From: Simon RiggsDate: 2008-07-12 09:20:37
Subject: Re: Rollback in Postgres
Previous:From: Dave PageDate: 2008-07-12 08:40:37
Subject: Re: Rollback in Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group