Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: Question about stored procedures

    Hey guys,

    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

  2. #2
    Join Date
    Jun 2004
    Posts
    31
    Assuming you have a table of the ilk:

    CREATE TABLE php_err
    (
    code CHAR(4) PRIMARY KEY,
    some,
    other,
    fields..
    );

    You mean a function like this?

    CREATE OR REPLACE FUNCTION
    fn_get_err()
    RETURNS php_err.code%TYPE AS '
    DECLARE
    -- code we will return
    r_cod php_err.code%TYPE;

    -- temp var
    t_cnt INTEGER;

    BEGIN
    -- get next randome code value
    -- keep looking until we have no collisions
    LOOP
    -- 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');

    -- collision?
    SELECT INTO t_cnt COUNT(*) FROM php_err
    WHERE code = r_cod;

    EXIT WHEN t_cnt = 0;
    END LOOP;

    -- make an entry to preserve this code
    INSERT INTO php_err(code)
    VALUES (r_cod);

    RETURN r_cod;
    END;
    ' LANGUAGE plpgsql SECURITY DEFINER;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •