problem with sequence..... - Mailing list pgsql-sql

Hi good evening every one………

I have a problem with sequence ..

I created one sequence with the name of famaseq..

I am using this sequence in my function..

This is the function

Create or replace function getFamanewcase1(in_cda_no varchar,in_dak_id varchar,in_name_of_beneficiary varchar,in_relation varchar,

In_address varchar,in_amnt_athrzd int4,in_pay_through varchar,in_bank_code varchar,in_bank_acc_no varchar,in_amnt_comision int4,

In_from_date date,in_mo_comisionpaid bool,in_remrks varchar) returns integer as'

Declare

Pay_thrw varchar:=''bank'';

Begin

If pay_thrw =$7 then

Insert into family_allotment_trans (cda_no, dak_id, name_of_the_beneficiary, relation, address, amount_authorized,

pay_through,bank_code,bank_acc_no,amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id) values($1,$2,$3,$4,$5,$6,$7,

$8,$9,$10,$11,$12,$13,nextval(''famaseq''));

else

Insert into family_allotment_trans (cda_no,dak_id,name_of_the_beneficiary,relation,address,amount_authorized,pay_through,bank_code,

amount_of_comision,from_date,mo_comsn_paidby,remarks,famly_alltmnt_trans_id) values($1,$2,$3,$4,$5,$6,$7,$8,$10,$11,$12,$13,nextval(''famaseq''));

end if;

return 0;

end;

'language'plpgsql';

select getFamanewcase1('123456a','dak1','penchal','friend','bangalore',2000,'order','bc2','bc1',20,'2006-06-06','false','no remarks');

If I use this select statement with correct inputs that sequence is working properly..

But when I was passing wrong values to that function..Automaticaly sequence is creating but its not showing in view data..

If again I pass correct values to that function that values can see in view data with increment value of sequence

Any one can tell me how to stop that sequence value when ever I was passing wrong values to that function….

2 bc11 23456a dak2 vivek brother hyd 333 2006-06-25 bc2 f

4 bc2 123456a dak1 penchal friend bangalore 2006-08-08 2000 2006-06-06 bc1 false f no remarks 20

Actually second record has starts with 3 but here its showing 4 becs before passing values to second record I given wrong values but sequence Is incremented automaricaly..

Can u telll me when ever I am going to give wrong values to that function .. the sequence value must be same………

Thanks  &  Regards

Penchal reddy | Software Engineer           

Infinite Computer Solutions | Exciting Times…Infinite Possibilities...

SEI-CMMI level 5 | ISO 9001:2000

IT SERVICES | BPO                                                                                                                                                                          

Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government                                                       

Tel +91-80-5193-0000(Ext:503)| Fax  +91-80-51930009 | Cell No  +91-9980012376|www.infics.com          

Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this mail from your records.