HOWTO - Random character generation for primary key

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(at)postgresql(dot)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(at)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