This website uses cookies:
• from Cloudflare to identify trusted web traffic and protect this website. [__cfduid][read more]
• from Google Analytics to generate statistical data on how visitors uses this website (with IP anonymization). [_ga][read more]
• from Google AdSense (third party vendors, including Google) to deliver ads based on user prior visits to this website or other websites. [NID][read more]
Do you accept personalized website ads? AdSense cookies will be enabled/disabled based on your decision. You can change your mind at any time by accesing the ePrivacy button located at the bottom of every page. Enjoy your visit!


PostgreSQL, custom functions to generate random EAN13 and UUID (v4)

When dealing with a Postgres database that stores internal products/assets, there is a chance that we will need either a unique EAN13 code for each or a UUID (Universal Unique Identifier), many times both of them.

For dealing with EAN13 (or alike data types) there is the "isn" module.
For dealing with UUIDs there are the "pgcrypto" and "uuid-ossp" modules.

If, whatever the reason, installing the aforementioned modules is a no go, then, for generating valid random EAN13 and UUID codes, the following Postgres custom functions will do the job.

Postgres custom function to generate random, valid EAN13 codes

CREATE OR REPLACE FUNCTION gen_ean13_random()
  RETURNS text AS
$$
declare ean13 text; check_digit integer;
begin
	
	ean13 = (SELECT string_agg(to_hex(width_bucket(random(), 0, 1, 10)-1) ,'') FROM generate_series(1, 12));
	check_digit = (SELECT esum-mod(esum,10)+10-esum - CASE WHEN mod(esum,10) = 0 THEN 10 ELSE 0 END as ecd from (select sum( case when mod(row_number,2) = 0 THEN e::int * 3 ELSE e::int END) as esum from (select e, row_number() over() from  unnest(regexp_split_to_array(ean13,'')) e) q) q1);
	ean13 = ean13||check_digit::TEXT;
	
	return ean13;
end;
$$
LANGUAGE plpgsql IMMUTABLE;

Postgres custom function to generate random, valid UUIDs

CREATE OR REPLACE FUNCTION gen_uuid_random()
  RETURNS uuid AS
$$
    SELECT string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,'')::uuid FROM generate_series(1, 16);
$$
  LANGUAGE sql IMMUTABLE;

Use them whenever you need, on-demand, or better, to automate things, within table triggers.

label_outline

Share this post on your favorite networks.
© Copyright 2017 b247.eu.org | Just another information technology blog - All Rights Reserved