Hi all!

I was a bit frustrated there was no function to generate a unique
identifier for any oid.
Instead of complaining, I decided to automate the process as far as possible. :)

The result is a simple perl function to automatically generate a
function for each regclass able to generate a unique text identifier.

The function obj_unique_identifier(oid) will return a unique name for _any_ oid.

I have looked at the unique constraints for each system_catalog to
make sure all identifiers are unique.

Source code:
perl script to generate .sql file:
https://github.com/gluefinance/pov/blob/master/sql/schema/pov/functions/obj_unique_identifier.pl
output from perl script:
https://github.com/gluefinance/pov/blob/master/sql/schema/pov/functions/obj_unique_identifier.sql

I would highly appreicate feedback on the structure of the identifier.
It must be composed in a way which will guarantee uniqueness.

Example:

glue=# select obj_unique_identifier(refobjid) from pg_depend order by
random() limit 10;
                               obj_unique_identifier
------------------------------------------------------------------------------------
 pg_proc.pg_catalog.iso8859_1_to_utf8(integer, integer, cstring,
internal, integer)
 pg_operator.pg_catalog.float8.pg_catalog.float8.pg_catalog.-
 pg_operator.pg_catalog.money.pg_catalog.int4.pg_catalog.*
 pg_amproc.gin.pg_catalog.array_ops.pg_catalog._time.pg_catalog._time.4
 pg_operator.pg_catalog.int2.pg_catalog.int4.pg_catalog.-
 pg_class.pg_catalog.pg_statio_sys_sequences
 pg_amproc.gin.pg_catalog.array_ops.pg_catalog._bool.pg_catalog._bool.1
 pg_class.pg_catalog.pg_stat_all_indexes
 pg_class.pg_catalog.pg_type
 pg_proc.pg_catalog.pg_stat_get_function_time(oid)
(10 rows)



-- 
Best regards,

Joel Jacobson
Glue Finance

Responses

• Re: obj_unique_identifier(oid) at 2011-01-07 19:46:28 from Tom Lane

pgsql-hackers by date

Next:	From: Jim Nasby	Date: 2011-01-07 18:28:54
	Subject: Re: crash-safe visibility map, take three
Previous:	From: Chris Browne	Date: 2011-01-07 17:37:35
	Subject: Re: [COMMITTERS] pgsql: Implement remaining fields of information_schema.sequences view