fabiolimace

/**
 * Returns the password MD5.
 * 
 * @param p_password the password string
 * @param p_salt     the salt string
 * @param p_encode   the output encoding: 'base64', 'hex', 'escape'.
 */
CREATE FUNCTION fn_password_md5(p_password VARCHAR, p_salt VARCHAR, p_encode VARCHAR) RETURNS VARCHAR AS $$
DECLARE
	v_salt_hash BYTEA := NULL;

fabiolimace

/**
 * Generate a random-based UUID (v4)
 *
 * RFC-4122 compliant.
 *
 * Tags: uuid guid uuid-generator guid-generator generator random rfc4122 rfc-4122
*/
create or replace function fn_uuid_random_based() returns uuid as $$
declare
	v_bytes  bytea;

fabiolimace

/**
 * Returns a name-based UUID v3 (MD5) 
 *
 * RFC-4122 compliant.
 *
 * Tags: uuid guid uuid-generator guid-generator generator name hash md5 sha1 rfc4122 rfc-4122
 *
 * @param p_name      a string
 * @param p_namespace a UUID string

fabiolimace

/*
 * MIT License
 *
 * Copyright (c) 2023-2024 Fabio Lima
 * 
 *  Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"), to deal
 *  in the Software without restriction, including without limitation the rights
 *  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 *  copies of the Software, and to permit persons to whom the Software is

fabiolimace

/**
 * Returns a time-based UUID (UUIDv1).
 *
 * RFC-4122 compliant.
 *
 * Tags: uuid guid uuid-generator guid-generator generator time rfc4122 rfc-4122
 */
create or replace function uuid1() returns uuid as $$
declare
	v_time timestamp with time zone:= null;

fabiolimace

/**
 * Generate a random-based UUID (v4)
 *
 * RFC-4122 compliant.
 *
 * Tags: uuid guid uuid-generator guid-generator generator random rfc4122 rfc-4122
*/
select (overlay(overlay(md5(random()::text || random()::text || current_timestamp::text) placing '4' from 13) placing ( to_hex(8 + (extract(microseconds from statement_timestamp())::integer % 4)) ) from 17))::uuid;

fabiolimace

/**
 * Returns a Time Sortable ID with millisecond precision.
 * 
 * Time component: 42 bits (2^42 = ~69 years)
 * 
 * Random component: 22 bits (2^22 = 4,194,304)
 * 
 * The time component is the count of milliseconds since 2020-01-01T00:00:00Z.
 *
 * Tags: tsid ulid snowflake id-generator generator time sortable sort order id

fabiolimace

/**
 * Returns a Time Sortable ID with microsecond precision.
 * 
 * Time component: 52 bits (2^52 = ~71 years)
 * 
 * Random component: 12 bits (2^12 = 4,096)
 * 
 * The time component is the count of microseconds since 2020-01-01T00:00:00Z.
 *
 * Tags: tsid ulid snowflake id-generator generator time sortable sort order id

fabiolimace

-- Find table
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_name ILIKE '%tablename%';

fabiolimace

package your.package.name;

import java.security.SecureRandom;
import java.time.Instant;
import java.util.UUID;

/**
 * A UUID generator that creates a COMB GUID with nanoseconds resolution.
 * 
 * It borrows the main idea from ULID and COMB generators: a concatenation of