HOWTO - Random character generation for primary key
От | Jean-Michel POURE |
---|---|
Тема | HOWTO - Random character generation for primary key |
Дата | |
Msg-id | 200205021104.06992.jm.poure@freesurf.fr обсуждение исходный текст |
Список | pgsql-admin |
Le Jeudi 2 Mai 2002 04:01, Alan Wayne a écrit : > In foxpro, I've been using the provided 10-character > random generator to provide for the primary keys on my > data files. As far as I can tell, PostgreSQL only uses > an auto-incrementing serial field for its primary > keys. So as far as I can see, I'm left with the > options of somehow converting all my existing records > (with their relationships intact) to some form of > integer key, or providing a similiar random character > generator from PostgreSQL. So what to do???? Dear Alan, Do not hesitate to CC pgsql-general@postgresql.org and pgsql-admin so that anyone can participate. I don't know if plain SQL supports loops, I guess not (?). Writing a primary key generator is quite easy under PostgreSQL using PLpgSQL. So let's go for PLpgSQL: First of all, enable PLpgSQL in your database: Under postgresql user: postgres@locahost>createlang plpgsql database_name Then add this PLpgSQL script to your database: CREATE FUNCTION "random_string"("int4") RETURNS "varchar" AS ' DECLARE iLoop int4; result varchar; BEGIN result = ''''; IF ($1>0) AND ($1 < 255) THEN FOR iLoop in 1 .. $1 LOOP result = result || chr(int4(random()*26)+65); END LOOP; RETURN result; ELSE RETURN ''f''; END IF; END; ' LANGUAGE 'plpgsql'; chr() is multi-byte safe. This means it will work for an UTF-8 or ASCII database. Not all PostgreSQL system functions are commented and described. A good way to learn how to use these functions is to run pgAdmin2 (http://pgadmin.postgresql.org) and choose the "'display system objects" option. Each function has a small comment which provides a small description. PLpgSQL syntax is close to Pascal and SQL. More information can be found on: http://www.postgresql.org/idocs/index.php?programmer-pl.html PostgreSQL advantage compared to other open-source systems, like MySQL for example, is the ability to write server-side code. It is highliy recommanded to migrate some of your existing Foxpro / Access code to PostgreSQL server-side, for the following reasons: - a server-side application has a better transactional behavior (ex: perform complex actions when you add / drop / update a table within transactions). - a PostgreSQL server-side application is ***much faster*** than a flat table application (PostgreSQL is able to process complex scripts involving several tables in ONE client-side query. In some situations, it can boost the speed by a 10 to 100 factor). After migration, your Foxpro / Access application should look like a simple data "viewer" or "browser" application. Furthermore, it allows you to add a Web interface (ex: PHP) quite easily because, again, PHP will be used for data viewing, not data processing. If you need more information, do not hesitate to get back to me and/or post comments on the list. Cheers, Jean-Michel
В списке pgsql-admin по дате отправления: