HOWTO - Random character generation for primary key

From: Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>
To: Alan Wayne <alanjwayne(at)yahoo(dot)com>, Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: HOWTO - Random character generation for primary key
Date: 2002-05-02 09:04:06
Message-ID: 200205021104.06992.jm.poure@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message postgres 2002-05-02 09:30:02 Re: HOWTO - Random character generation for primary key
Previous Message Hal Lynch 2002-05-01 22:43:26 Re: postgresql authentication

Browse pgsql-general by date

  From Date Subject
Next Message postgres 2002-05-02 09:30:02 Re: HOWTO - Random character generation for primary key
Previous Message Jakub Ouhrabka 2002-05-02 07:52:49 Re: FATAL: stuck spinlock