PostgreSQL field reset to 1

[Suhaimin]

Hello,

I want to make a storeproc in PostgreSQL, if field invoiceno = 1000 then i want to reset to 1 again.. i want to call with Tunistoredproc. anyone can help?

i tried to make storeproc:
update invoice
if "invoiceno" = 1000 then
set "invoiceno"=1
else
set "invoiceno"= "invoiceno" +1;
end if;


thanks a lot..

[DemetrionQ]

Hello.

If you need to set the invoiceno value to 1 in all records, where invoiceno = 1000, and increase invoiceno by 1 in others, you can use the following function:

Code: Select all

CREATE OR REPLACE FUNCTION invoice_modify()
  RETURNS void AS
$$
begin
  update invoice set invoiceno = 0 where invoiceno = 1000;
  update invoice set invoiceno = invoiceno + 1 where invoiceno <> 1000;
end;
$$
LANGUAGE plpgsql;

and call it as follows:

Code: Select all

  UniStoredProc1.StoredProcName := 'invoice_modify';
  UniStoredProc1.Execute;

If the invoiceno field is primary key or unique key, execution of this function can cause errors.

[Suhaimin]

Hello Master DemetrionQ,


invoiceno is unique. actually what i want is format invoiceno = 'yyyy/mm/dd/'+ increment number.. if date change increment number start from 1, but i dont know to implementation in store procedure in postgresql. thank a lot

[DemetrionQ]

Please provide the table creation script.

[Suhaimin]

- Table: "INVOICE"

-- DROP TABLE "INVOICE";

CREATE TABLE "INVOICE"
(
"ID" bigint NOT NULL,
"INVOICEDATE" date,
"INVOICENO" character varying(20),
CONSTRAINT "INVOICEID" PRIMARY KEY ("ID" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE "INVOICE"
OWNER TO postgres;


ID is unique will be filled with a program.. then after insert i want update invoiceno will be filled with a tunistoredproc with format '2013/07/09/1', '2013/07/09/2', '2013/07/09/3' and so on, then tommorow transaction will be '2013/07/10/1','2013/07/10/2','2013/07/10/3' and so on. how to do that with a storedproc and then i call from unistoredproc..thanks a lot

[DemetrionQ]

To solve the issue, you can add an integer "TODAYID" field to the table, e.g., as follows:

Code: Select all

ALTER TABLE "INVOICE" ADD COLUMN "TODAYID" integer;

and create a trigger:

Code: Select all

CREATE OR REPLACE FUNCTION  invoice_modify() RETURNS TRIGGER AS $$
BEGIN
  IF NEW."INVOICEDATE" IS NULL THEN
    NEW."INVOICEDATE" = current_date;
  END IF;
    
  SELECT (coalesce(max("TODAYID"), 0) + 1) FROM "INVOICE" 
    WHERE "INVOICEDATE" = NEW."INVOICEDATE"
  INTO NEW."TODAYID";

  NEW."INVOICENO" = to_char(NEW."INVOICEDATE", 'YYYY/MM/DD') || '/' || NEW."TODAYID";
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER invoice_modify_after_insert
    BEFORE INSERT ON "INVOICE" 
    FOR EACH ROW
    EXECUTE PROCEDURE  invoice_modify();

Then the needed changes will be applied automatically when inserting a new record.

[Suhaimin]

Hello Master DemetrionQ,


When I call that Function invoice_modify() in UniQuery.beforepost or uniquery.afterpost or uniquery.afterinsert ?? then if computer A, B ,C and D run that function on the same time with Unistoredprocedure, is it possible we have that invoiceno the same value ?? thanks a lot

[DemetrionQ]

1) When using the trigger, you don't need to call the invoice_modify() function manually: it will be automatically called by the trigger when adding a new record.

2) In order for the TODAYID field not to repeat during a day at the multi-user access, you can use a sequence for the TODAYID field and re-create this sequence every day. But using a sequence, you will encounter the following specificities:
- step between values of a field filled in by a sequence can be more than 1;
- if you use one sequence, then, when inserting records with an old date (for example, yesterday), the number for the TODAYID field will still be taken from the current sequence.

[Suhaimin]

Hello Master DemetrionQ,


I dont understand how to use a sequence for the TODAYID field, pls give me example how to applied a sequence for TODayID field..thanks a lot

[DemetrionQ]

Hello.

You can use sequence for the TODAYID field as follows:

1) create a sequence:

Code: Select all

   CREATE SEQUENCE todayid_sequence;

2) replace the following SQL statement in the invoice_modify() trigger function:

Code: Select all

  SELECT (coalesce(max("TODAYID"), 0) + 1) FROM "INVOICE"
    WHERE "INVOICEDATE" = NEW."INVOICEDATE"
  INTO NEW."TODAYID";

with the following one:

Code: Select all

  SELECT nextval('todayid_sequence') INTO NEW."TODAYID";

3) reset the sequence value at the beginning of every day in the following way:

Code: Select all

  SELECT setval('todayid_sequence', 1, False);

For detailed information on sequences, please refer to the official documentation of PostgreSQL, at http://www.postgresql.org/docs/9.2/stat ... uence.html

[Suhaimin]

hello Master DemetrionQ,


But how program know today is a beginning of day so we must reset sequence?thanks a lot

[DemetrionQ]

Hello.

You can reset the sequence value at the beginning of every day using pgAgent (a job scheduling agent for PostgreSQL). Detailed information on it can be found here: http://www.pgadmin.org/docs/1.16/pgagent.html

[Suhaimin]

Thanks Master DemetrionQ

[DemetrionQ]

If any other questions come up, please contact us.