Hot File

Creating newid for PostgreSQL

View: 2825    Dowload: 0   Comment: 0   Post by: hanhga  
Author: none   Category: PostgreSQL   Fields: Education - Schools

0 point/3 review File has been tested

Without installing the uuid-ossp, PostgreSQL doesn't support an equivalent to the Microsoft SQL Server function newid(), so I set out to create one.

Introduction

Background

I started out by reading the uuid RFC, the Wikipedia article on uuids and the documentation on newid(). It turns out that it generates a version 4 uuid. Those are based on random or pseudo-random numbers, with certain nibbles set to certain values. To explain what that means, in simple terms if we replace all the random numbers with 'x', you will see that at position 14 there is the number '4', and at position 19, is a 'y'.

xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx

The number '4' at position 14 is obviously for the version number, at that position you will be able to always find the version number. The 'Y' at position 19 you are supposed to set the bits specifically, if you can stand reading the RFC, you will end up with 4 possible values '8', '9', 'A' or 'B'.

All of this is well and good, but from inside SQL we don't have bit manipulation functions, but we do have string manipulation functions.

To complicate matters, the random function only generates a "random value in the range 0.0 <= x < 1.0". Furthermore, it only returns a double.

So I started looking at other versions. Version 3 takes advantage of something I already knew, an MD5 signature is the same length as a uuid. That version uses namespaces to create a unique blob of data, and then creates an MD5 hash of that data, then replaces position 14, with a '3'.

The problem with using version 3 uuids is if we stick to the specification, it is supposed to be able to generate the same uuid given the same inputs. This isn't exactly what I want.

So my thought was to borrow a little from both, to create a unique bit of data, that should create a random hash. To start, I will grab the current database name, the current user name, the current timestamp, and a call to random(). Finally I will create an MD5 hash of that data.

CREATE OR REPLACE FUNCTION dbo.newid()
  RETURNS uuid AS
$BODY$
 SELECT CAST(md5(current_database()|| user ||current_timestamp ||random()) as uuid)
$BODY$
  LANGUAGE 'sql' VOLATILE

I skipped replacing the values correctly, because of the lack of fast manipulation functions. If at some point you get the uuid-ossp module installed, then you can change the function to the following:

CREATE OR REPLACE FUNCTION dbo.newid()
  RETURNS uuid AS
$BODY$
 SELECT uuid_generate_v4()
$BODY$
  LANGUAGE 'sql' VOLATILE

Using the Code

Obviously you could simply do a select:

SELECT dbo.newid()

However the reason for wanting this function in the first place is to prepopulate columns in tables, and so you can create tables like this:

CREATE TABLE dbo.sometable
(
  id uuid NOT NULL DEFAULT dbo.newid(),
  created_by character varying(50) NOT NULL,
  created_on timestamp with time zone NOT NULL DEFAULT now(),
  modified_by character varying(50) NOT NULL,
  modified_on timestamp with time zone NOT NULL DEFAULT now()
)
WITH (
  OIDS=FALSE
);

Points of Interest

This function obviously isn't creating uuids of any strict version number, but it does create uuids that have a high probability of being unique, allowing an easier move from SQL Server to PostgreSQL.

I created the function in the dbo schema rather than in the default "public

Creating newid for PostgreSQL

Creating newid for PostgreSQL Posted on 03-04-2014  Without installing the uuid-ossp, PostgreSQL doesn't support an equivalent to the Microsoft SQL Server function newid(), so I set out to create one. 5/10 2825

Comment:

To comment you must be logged in members.

Files with category

  • Accessing: MySQL Database using MySQL C API

    View: 2332    Download: 2   Comment: 0   Author: none  

    Accessing: MySQL Database using MySQL C API

    Category: PostgreSQL
    Fields: Other

    0.75/6 review
    This article was written for beginners, developers that do not know how to access MySql database using MySQL C API and would like to have a small introduction.

  • Full Database Abstraction Layer Generator

    View: 2418    Download: 0   Comment: 0   Author: none  

    Full Database Abstraction Layer Generator

    Category: PostgreSQL
    Fields: Other

    0/23 review
    This article presents yet another Data Abstraction Layer generator (popular topic). The DAL structure is directly inspired from the DotNetNuke, DAL document and the SQL generation is done using SQLpp. The generator takes care of pretty much...

  • Code .NET 2.0; Build Java; Run Linux

    View: 1956    Download: 0   Comment: 0   Author: none  

    Code .NET 2.0; Build Java; Run Linux

    Category: PostgreSQL
    Fields: Other

    1.5/3 review
    This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers. This is a showcase review for...

  • Postgres Database Backup/Restore From C#

    View: 2818    Download: 2   Comment: 0   Author: none  

    Postgres Database Backup/Restore From C#

    Category: PostgreSQL
    Fields: Other

    0/9 review
    This article provides Postgres database backup/restore functionality. By using this application we can check whether a Postgres database is installed or not in a system. If a Postgres database is installed then the Postgres databases list is...

  • Installing Redmine on Windows in production

    View: 5265    Download: 0   Comment: 0   Author: none  

    Installing Redmine on Windows in production

    Category: PostgreSQL
    Fields: Other

    0/9 review
    This is a very simple step by step guide to install Redmine on Windows server in production. This guide is applicable to IIS 7+ based servers, which include Windows Server 2008, 2008 R2, Windows Vista and Windows 7. Latter two should not be used for...

  • A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    View: 3076    Download: 0   Comment: 0   Author: none  

    A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

    Category: PostgreSQL
    Fields: Other

    0.5625/8 review
    Since MSSQL is not holding the lion share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the...

  • Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    View: 4164    Download: 5   Comment: 0   Author: none  

    Creating an Interactive Map in ASP.NET 2.0 Using SharpMap

    Category: PostgreSQL
    Fields: Other

    0/2 review
    Showing maps on the web is becoming more and more common. The most well-known ones are Google Maps and Microsoft's Virtual Earth. Creating your own websites with interactive maps require you to get hold on the map-data and convert this data to...

  • DevShot - Database Snapshots for Developers

    View: 867    Download: 0   Comment: 0   Author: none  

    DevShot - Database Snapshots for Developers

    Category: PostgreSQL
    Fields: Other

    0/2 review
    A few weeks back, I got an idea of writing a simple tool for switching database states between coding cycles. I thought this would be a good tool for anyone who is working on database related projects and have to switch between database states since...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd