Thread: Question about stored procedures
08-09-04, 04:02 #1Registered User
- Join Date
- Aug 2004
Unanswered: Question about stored procedures
i got a question that bothers me quite a while,
been looking everywhere but found no answer
My idea is to automate this procedure for better performance...
Can anybody in here tell me if its possible to auto create
a 4 digit randomly code 1st digit alpha 2nd -4th numeric
that gets looked up in the db if its there new code is generated
if its not give the code back to my php proggy?
Its thought to be an ErrorCode Creator....
I would like to do it in PostGreSQl with a stored procedure
Any Help would be appreciated
08-14-04, 17:26 #2Registered User
- Join Date
- Jun 2004
Assuming you have a table of the ilk:
CREATE TABLE php_err
code CHAR(4) PRIMARY KEY,
You mean a function like this?
CREATE OR REPLACE FUNCTION
RETURNS php_err.code%TYPE AS '
-- code we will return
-- temp var
-- get next randome code value
-- keep looking until we have no collisions
-- randomly find a 4 digit code
-- Note that there is a nasty race condition
-- here since we could allocate a number and
-- some other process could allocate the
-- same one and get it inserted before we
-- get a chance to insert it, causing a primary
-- key violation. A better Pg coder could
-- eliminate this race condition.
SELECT INTO r_cod CHR(CAST((RANDOM()*25) AS INTEGER)+ASCII('A')) || TO_CHAR(FLOOR(RANDOM()*999), 'FM009');
SELECT INTO t_cnt COUNT(*) FROM php_err
WHERE code = r_cod;
EXIT WHEN t_cnt = 0;
-- make an entry to preserve this code
INSERT INTO php_err(code)
' LANGUAGE plpgsql SECURITY DEFINER;