Sequence w/o 'holes', my implementation (in pl/pgsql)?

hello,

i wrote a sequence counter that does not produce holes in pl/pgsql.
but i don't really know if thats a good one.
it would be very nice if someone could help me out with my three questions
about this code below:

- what do i have to do to make this one multi user save (i don't think it is
right now)?
- is there a way to get this running without all these dynamic queries (e.g.
passing a whole table to a pl/pgsql function), i don't think it is very
performant right now.
- is there a way to make the trigger-function independent on the row name?
  in my case i use a column OLD.id but i want that "id" to be passed to the
trigger as a string:
  BUT i can't use OLD in a dynamic query, and i can't find a way to use the
column name stored in a string on OLD either.


About the functions:

  gapless_create (TEXT seq_name, INT start_value)
     creates a table with one column (free_id) filled with one row of the
start_value;
     there is a rule assigned to that table which prevents the last value
from being deleted, and increases it instead.

  gapless_drop (TEXT seq_name)
     drops the table.

  gapless_get (TEXT seq_name)
     used to get a free sequence number.
     returns the smallest value in the table and deletes it.

  gapless_free (TEXT seq_name, INT value)
     puts a number that has freed in the table, so get function can return
it.
     this value must be smaller than the biggest value of the table or there
will be holes.

  tr_gapless_free()
     same as free, but this has to be used as a trigger function.



an example of being used:
-------------------------

SELECT gapless_create ( 'engel_id_seq' , 0);
CREATE TABLE engel
(
    id    INT    PRIMARY KEY DEFAULT gapless_get ( 'engel_id_seq' ),
    name    TEXT
);

CREATE TRIGGER engel_trigger AFTER DELETE ON engel
    FOR EACH ROW EXECUTE PROCEDURE tr_gapless_free( 'engel_id_seq' );


INSERT INTO engel (name) VALUES ('sachiel');
INSERT INTO engel (name) VALUES ('shamshel');
DELETE FROM engel WHERE name='sachiel';
INSERT INTO engel (name) VALUES ('ramiel');
INSERT INTO engel (name) VALUES ('gaghiel');


----
---- the Functions:
-------------------

CREATE FUNCTION gapless_create ( text , int ) RETURNS int AS '
    BEGIN
        EXECUTE '' CREATE TABLE ''||$1||'' ( free_id INT PRIMARY KEY );
            '';
        EXECUTE '' INSERT INTO  ''||$1||'' ( free_id ) VALUES ( ''||$2||'' );
            '';
        EXECUTE '' CREATE RULE  ''||$1||''_gl_rule  AS ON DELETE TO ''||$1||''
               WHERE 1 = (SELECT count(*) FROM ''||$1||'' )
               DO INSTEAD UPDATE ''||$1||'' SET free_id = 1+(SELECT max(free_id) FROM
''||$1||'' );
            '';
        RETURN $2;
    END;
'    LANGUAGE 'plpgsql';


CREATE FUNCTION gapless_drop ( text ) RETURNS int AS '
    BEGIN
        EXECUTE '' DROP TABLE ''||$1||'';
            '';
        RETURN 0;
    END;
'    LANGUAGE 'plpgsql';


CREATE FUNCTION gapless_get ( text ) RETURNS int AS '
    DECLARE
        rec RECORD;
        retval INT;
    BEGIN
        FOR rec IN EXECUTE ''  SELECT min(free_id) AS min_id FROM ''||$1||'';  ''
LOOP
            retval := rec.min_id;
        END LOOP;

        EXECUTE '' DELETE FROM ''||$1||'' WHERE free_id =
''||to_char(retval,''9999999999'')||'';
            '';
        RETURN retval;
    END;
'    LANGUAGE 'plpgsql';


CREATE FUNCTION gapless_free ( text , int) RETURNS int AS '
    BEGIN
        EXECUTE '' INSERT INTO  ''||$1||'' ( free_id ) VALUES ( ''||$2||'' );
            '';

        RETURN $2;
    END;
'    LANGUAGE 'plpgsql';


CREATE FUNCTION tr_gapless_free() RETURNS OPAQUE AS '
    DECLARE
        tmpvar INT;
    BEGIN

        IF TG_NARGS = 0 THEN
            RAISE EXCEPTION ''Function: fr_gapless_free MUST have an Argument'';
        END IF;

        tmpvar = OLD.id;
        ----------------  WOULD BE NICE IF id COULD BE DYNAMIC

        EXECUTE '' INSERT INTO ''||TG_ARGV[0]||'' ( free_id ) VALUES (
''||to_char(tmpvar,''9999999999'')||'' );
            '';
        RETURN NULL;
    END;
'    LANGUAGE 'plpgsql';


-- END OF CODE


Thanks for reading and/or your help
     Teschi


--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net