Thread: Plpgsql and cursors
hi,
' LANGUAGE 'plpgsql';
i'm testing postgresql database and especially the procedural language plppgsql.
I want to 'translate' an Oracle pl/sql procedure which looks like this :
create function cal_montant_ht_com(id_com int8) RETURN NUMBER isI try this :cursor cur1 is select quantite_detcom,prix_ht_detcom from detail_commandes where fk_commande_detcom=id_com;begin
quantite_detcom NUMBER;
prix_ht_detcom NUMBER;
montant_ht NUMBER;montant_ht:=0;end cal_montant_ht_com;
OPEN cur1;
LOOP
FETCH cur1 INTO quantite_detcom, prix_ht_detcom;
EXIT WHEN cur1%notfound;
montant_ht:=montant_ht+(quantite_detcom*prix_ht_detcom);
END LOOP;
close cur1;
RETURN (montant_ht);
create function cal_montant_ht_com(int8) RETURNS float8 AS '
cursor cur1 is select quantite_detcom,prix_ht_detcom from detail_commandes where fk_commande_detcom=$1;begin
quantite_detcom int8;
prix_ht_detcom float8;
montant_ht float8;
montant_ht:=0;END;
OPEN cur1;
LOOP
FETCH cur1 INTO quantite_detcom, prix_ht_detcom;
EXIT WHEN cur1%notfound;
montant_ht:=montant_ht+(quantite_detcom*prix_ht_detcom);
END LOOP;
close cur1;
RETURN montant_ht;
' LANGUAGE 'plpgsql';
but it still doesn't work (error at line 2, the declaration of the cursor) and haven't any other idea;
Could you help me ?
olivier Allain.