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

From:    Teschi@gmx.de
Date:    Wed, 22 May 2002 22:10:52 +0200 (MEST)
Subject: [GENERAL] Sequence w/o 'holes', my implementation (in pl/pgsql)?


As for your second question, the minimum gap number seems to
be retrieved by ones(see below) as well as yours . I wouldn't think
they have an adverse effect on the performance. But, I haven't yet
done its test.


Regards,
Masaru Sugawara


 they don't have an adverse effect on the performance.

--------------------------------------------------------
CREATE TABLE engel (id int4 PRIMARY KEY NOT NULL,
                    name text,
                    CONSTRAINT ct_id_check CHECK(id > 0));

CREATE VIEW vi_engel AS SELECT name FROM engel;

CREATE OR REPLACE FUNCTION fn_get_gap() RETURNS int4 AS '
    declare
        rec RECORD;
        iCurrent int4 := 1;
        iBefore int4 := 0;
    BEGIN
          FOR rec IN SELECT * FROM engel ORDER BY id LOOP
               IF rec.id > iCurrent THEN
                   EXIT;
               END IF;
               iCurrent := iCurrent + 1;
               iBefore := rec.id;
         END LOOP;
    RETURN iBefore + 1;
    END;
' LANGUAGE 'plpgsql';

CREATE RULE rl_gapless AS ON INSERT
    TO vi_engel
    DO INSTEAD INSERT INTO engel VALUES(fn_get_gap(),NEW.name);


-------------------------------------------------------
renew=# INSERT INTO vi_engel (name) VALUES ('sachiel');
renew=# INSERT INTO vi_engel (name) VALUES ('shamshel');
renew=# DELETE FROM engel WHERE name='sachiel';
renew=# INSERT INTO vi_engel (name) VALUES ('ramiel');
renew=# INSERT INTO vi_engel (name) VALUES ('gaghiel');
renew=# SELECT * FROM engel;
id |   name
---+----------
 2 | shamshel
 1 | ramiel
 3 | gaghiel
(3 rows)

renew=# select version();
                              version
--------------------------------------------------------------------
PostgreSQL 7.2.1 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66



>
> 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