hi,
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 is cursor cur1 is select quantite_detcom,prix_ht_detcom from detail_commandes where fk_commande_detcom=id_com;
quantite_detcom NUMBER;
prix_ht_detcom NUMBER;
montant_ht NUMBER;
begin montant_ht:=0;
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);
end cal_montant_ht_com;
I try this :
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;
quantite_detcom int8;
prix_ht_detcom float8;
montant_ht float8;
begin
montant_ht:=0;
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;
END;
' 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.
