help cursor postgresql......

Database dan Structured Query Language

help cursor postgresql......

Postby batozai » 20 May 2011, 23:43

master master semua....saya ada problem nih...
saya membuat function di postgresql, dan udah berjam jam tak utak atik eror eror melulu....tak cari2 di google ga dapat2 solusinya juga....mohon bantuan para master untuk memperbaiki function saya ini

Code: Select all
  1. CREATE OR REPLACE FUNCTION func_customerlunas (

  2.   "picustomerid" bigint,

  3.   "ptglawal" date,

  4.   "ptglakhir" date

  5. )

  6. RETURNS TABLE (

  7.   "invoice_number" varchar,

  8.   "customer" varchar,

  9.   "tgl_jatuh_tempo" date,

  10.   "tot_tagihan" bigint,

  11.   "tgl_lunas" date

  12. ) AS $$

  13.  

  14.  declare

  15.     rec record;

  16.     sSQL text;

  17.     ltglawal text;

  18.     ltglakhir text;

  19.     curdata refcursor;

  20.  begin

  21.     ltglawal = to_char(ptglawal,'YYYY-MM-DD');

  22.     ltglakhir = to_char(ptglakhir,'YYYY-MM-DD');

  23.    

  24.    sSQL ='select slv.id,slv.invoice_number,cst.long_name,slv.tgl_lunas,'

  25.     ||'(slv.totprice - (round((slv.diskon/100)*slv.totprice))) as "tot_tagihan",slv.tgl_jatuh_tempo'

  26.     ||' from tsales_invoice slv'

  27.     ||' left join tcustomer cst on slv.cust_id = cst.id and slv.islunas = true'

  28.     ||' left join tsalesman sl on sl.id = cst.salesman'      

  29.     ||' where slv.tgl_lunas between '||quote_literal(ltglawal)||' and '||quote_literal(ltglakhir)

  30.     ||' and cst.id ='||quote_literal(cast(picustomerid as CHAR))    

  31.     ||' order by cst.long_name';

  32.  

  33.     open curdata FOR EXECUTE sSQL into rec;

  34.    

  35.  for rec in curdata

  36.  

  37.   loop    

  38.    

  39.     invoice_number = rec.invoice_number;

  40.     customer = rec.long_name;

  41.     tgl_jatuh_tempo = rec.tgl_jatuh_tempo;

  42.     tot_tagihan = rec.tot_tagihan;

  43.     tgl_lunas = rec.tgl_lunas;

  44.     return next rec;

  45.   end loop;

  46.  

  47.   RETURN;

  48. end

  49. $$

  50. LANGUAGE 'plpgsql';



error messagenya adalah :
ERROR: cursor FOR loop must use a bound cursor variable
LINE 24: for rec in curdata


saya rasa errornya di bagian
Code: Select all
  1. for rec in curdata



tetapi saya belum bisa dapat solusinya.....
pengennya sih membuat query dinamis dengan kursor gitu.....


terimas kasih banyak saya ucapkan sebelumnya
i believe bill gates doesn't make his success by imitating what is exist
batozai
Junior Member
 
Posts: 46
Joined: 08 Jun 2010, 22:57

RE: help cursor postgresql......

Postby mambamaestro » 21 May 2011, 08:25

cursor dynamic? well.. kalo bisa, baru kali ini ane tahu
mambamaestro
Member
 
Posts: 156
Joined: 06 May 2010, 14:14
Location: Surabaya

RE: help cursor postgresql......

Postby batozai » 23 May 2011, 23:54

@mbah waaahh aku jadi semakin pesimis.......hahahahaha
i believe bill gates doesn't make his success by imitating what is exist
batozai
Junior Member
 
Posts: 46
Joined: 08 Jun 2010, 22:57

RE: help cursor postgresql......

Postby Manz » 31 May 2011, 13:59

@batozai :

kalo :
Code: Select all
  1. sSQL ='select slv.id,slv.invoice_number,cst.long_name,slv.tgl_lunas,'

  2.     ||'(slv.totprice - (round((slv.diskon/100)*slv.totprice))) as "tot_tagihan",slv.tgl_jatuh_tempo'

  3.     ||' from tsales_invoice slv'

  4.     ||' left join tcustomer cst on slv.cust_id = cst.id and slv.islunas = true'

  5.     ||' left join tsalesman sl on sl.id = cst.salesman'      

  6.     ||' where slv.tgl_lunas between '||quote_literal(ltglawal)||' and '||quote_literal(ltglakhir)

  7.     ||' and cst.id ='||quote_literal(cast(picustomerid as CHAR))    

  8.     ||' order by cst.long_name';

  9.  

  10. for rec in sSQL

  11.  

  12.   loop    

  13.    

  14.     invoice_number = rec.invoice_number;

  15.     customer = rec.long_name;

  16.     tgl_jatuh_tempo = rec.tgl_jatuh_tempo;

  17.     tot_tagihan = rec.tot_tagihan;

  18.     tgl_lunas = rec.tgl_lunas;

  19.  

  20.   end loop;

  21.  

  22.   RETURN;

  23. end

  24. $$

  25. LANGUAGE 'plpgsql';



ngak ada postgrenya ... tapi nyobain secara logik saja :D
-= Man'z =- yah begitulah mereka memanggilku...

Catatan koe : -=| Manz |=- it's me :wink:
User avatar
Manz
Member
 
Posts: 160
Joined: 06 May 2010, 08:09


Who is online

Users browsing this forum: No registered users and 1 guest

cron