From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: John Fabiani <johnf@jfcomputer.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: interesting sequence
Date: Wed, 06 Jul 2011 21:47:06 +1200
Message-ID: <4E142F1A.8060900@archidevsys.co.nz> (raw)
In-Reply-To: <201107050652.22187.johnf@jfcomputer.com>
References: <201107050652.22187.johnf@jfcomputer.com>
On 06/07/11 01:52, John Fabiani wrote:
> Hi,
>
> I have a special need to create a sequence like function.
>
> "O-20110704 -2" which is
> "O" for order (there are other types)
> "20110704" is for July 4, 2011
> '2' the second order of the day for July 4, 2011
>
> I of course can get the type and date. What I don't know is how to get is the
> last number. It would seem to be that I would need a loop to determine if the
> next number existed.
>
> LOOP
> --Check to see if the string exist in a table
> -- count = count +1
> -- until I don't find the string
> END LOOP;
>
> but then I thought I could do something like
>
> for $1 in (select string from sometable)
> LOOP
> count = count + 1
>
> or something like this
>
> for i in 1..999 LOOP
> -- check for the existence of the string in a table using 'i'
> -- there will never be 999 orders in one day.
> END LOOP
>
>
> So here is the question what would be the best way for a multi-user system?
> If someone has a better thought - it would be helpful.
>
> BTW I did NOT design the number - in fact it seems silly to me.
>
> Johnf
>
Hi John,
How about using a table to hold the latest sequence for each order type
and date, along with a function to insert a new order?
(I've included the code to test the idea and the results, I am using
9.1beta2, but it should not make any difference - I think!):
DROP TABLE IF EXISTS my_order;
DROP TABLE IF EXISTS order_sequence;
CREATE TABLE my_order
(
order_num text PRIMARY KEY,
payload text
);
CREATE TABLE order_sequence
(
type int,
day date,
seq int NOT NULL,
PRIMARY KEY (type, day)
);
CREATE OR REPLACE FUNCTION create_my_order
(
IN type int,
IN day date,
IN payload text
) RETURNS VOID
AS
$$
DECLARE
v_order_num text;
v_seq_old int;
v_seq_new int;
BEGIN
SELECT
os.seq
FROM
order_sequence os
WHERE
os.type = create_my_order.type AND
os.day = create_my_order.day
INTO
v_seq_old;
IF v_seq_old IS NULL THEN
v_seq_new := 1;
INSERT INTO order_sequence(type, day, seq)
VALUES (type, day, v_seq_new);
ELSE
v_seq_new := v_seq_old + 1;
UPDATE
order_sequence AS os
SET
seq = v_seq_new
WHERE
os.type = create_my_order.type AND
os.day = create_my_order.day;
END IF;
v_order_num := type::text ||
'-' ||
to_char(day, 'YYMMDD') ||
'-' ||
v_seq_new::text;
INSERT INTO my_order(order_num, payload)
VALUES (v_order_num, payload);
END;
$$ LANGUAGE plpgsql
VOLATILE
;
SELECT create_my_order (0, '2010-03-24', 'order #1 details');
SELECT create_my_order (0, '2010-03-24', 'order #2 details');
SELECT create_my_order (0, '2010-06-15', 'order #3 details');
SELECT create_my_order (5, '2010-03-24', 'order #4 details');
SELECT create_my_order (0, '2010-06-15', 'order #5 details');
SELECT create_my_order (3, '2010-06-14', 'order #6 details');
TABLE order_sequence;
TABLE my_order;
////////// This outputs the following:
type | day | seq
------+------------+-----
0 | 2010-03-24 | 2
5 | 2010-03-24 | 1
0 | 2010-06-15 | 2
3 | 2010-06-14 | 1
(4 rows)
order_num | payload
------------+------------------
0-100324-1 | order #1 details
0-100324-2 | order #2 details
0-100615-1 | order #3 details
5-100324-1 | order #4 details
0-100615-2 | order #5 details
3-100614-1 | order #6 details
(6 rows)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, johnf@jfcomputer.com
Subject: Re: interesting sequence
In-Reply-To: <4E142F1A.8060900@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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