pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with sequence.....
2+ messages / 2 participants
[nested] [flat]
* problem with sequence.....
@ 2006-08-16 11:31 Penchalaiah P. <penchalaiahp@infics.com>
0 siblings, 1 reply; 2+ messages in thread
From: Penchalaiah P. @ 2006-08-16 11:31 UTC (permalink / raw)
To: 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_authorize
d,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,'or
der','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.
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 the 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 email from your records.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: problem with sequence.....
@ 2006-08-16 14:17 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Penchalaiah P. <penchalaiahp@infics.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andrew Sullivan @ 2006-08-16 14:17 UTC (permalink / raw)
To: pgsql-sql
On Wed, Aug 16, 2006 at 05:01:09PM +0530, Penchalaiah P. wrote:
> 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....
I'm not entirely sure I understood you, but if what you're asking is
for the sequence not to increment on transaction rollback, then you
can't have it. The sequence system guarantees that the numbers will
be in increasing order (subject to rollover), but it does not
guarantee that there will be no gaps. This is to avoid some
unpleasant concurrency side-effects from the no-gaps approach. If
you really need that, then you have two choices:
1. Roll your own, using some sort of interlock table. This will
not play nicely with a lot of concurrent writes, however (which is
the disadvantage the current implementation is designed to avoid).
2. In recent PostgreSQL releases, you could use a savepoint. So,
get the nextval() of your serial number. Set a savepoint. Try your
insert. If that fails, roll back and save the currval() as VOIDed.
(This isn't completely safe. You can make it completely safe by
doing it in two transactions, but that's best left as an exercise for
the reader.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-08-16 14:17 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-16 11:31 problem with sequence..... Penchalaiah P. <penchalaiahp@infics.com>
2006-08-16 14:17 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox