求生成uuid的函数的写法

cowboydotnet

In your target Postgres tables, define your GUID fields as either char(36) or varchar(36).

In your SELECT from SQL Server, you will want to strip the '{}' characters from the source data.

To generate new GUID values, you will need to create a Postgres user defined function (like 'new_uuid()') to return a varchar value. An MS-like GUID can be created by hashing together your server's MAC address with a timestamp. The md5 function can be used to do this easily like this:

vWork1 := md5(vMAC || now());
vWork2 := SUBSTRING(vWork1, 1, 8) ||'-'|| SUBSTRING(vWork1,9,4) ||'-'|| SUBSTRING(vWork1,13,4) ||'-'|| SUBSTRING(vWork1,17,4) ||'-'|| SUSTRING(vWork1,21,12);
RETURN cast(vWork2 as varchar(36));

I'm doing this off the top of my head, but the code above is very much like implementations that I have done on my own Postgres instances.

这是我看到的一个鸟语帖子。哪个哥哥可以帮写出一段完成的new_uuid函数的定义。
我的

CREATE FUNCTION "New_Uuid"() RETURNS "char" AS
'vWork1 := md5(vMAC || now());
vWork2 := SUBSTRING(vWork1, 1, 8) ||\'-\'|| SUBSTRING(vWork1,9,4) ||\'-\'|| SUBSTRING(vWork1,13,4) ||\'-\'|| SUBSTRING(vWork1,17,4) ||\'-\'|| SUSTRING(vWork1,21,12);
RETURN cast(vWork2 as varchar(36));
'
LANGUAGE 'sql' VOLATILE;

总是要报错

ERROR:  syntax error at or near "vWork1" at character 1

cowboydotnet

[postgres@lacl-001 /root]$createlang -d dbname plpgsql

-- Function: new_uuid()

-- DROP FUNCTION new_uuid();

CREATE OR REPLACE FUNCTION new_uuid()
  RETURNS text AS
'
declare
vWork1 text;
vWork2 text;
vMac text;
begin
-- replace vMac with the mac of your own box
vMac=\'00:0D:56:FD:A0:DB\';
vWork1 := md5(vMAC || now());
vWork2 := SUBSTRING(vWork1, 1, 8) ||\'-\'|| SUBSTRING(vWork1,9,4) ||\'-\'|| SUBSTRING(vWork1,13,4) ||\'-\'|| SUBSTRING(vWork1,17,4) ||\'-\'|| SUBSTRING(vWork1,21,12);
RETURN cast(vWork2 as varchar(36));
end;
'
  LANGUAGE 'plpgsql' VOLATILE;

lacl=# SELECT new_uuid();
               new_uuid
--------------------------------------
77f377d1-07db-4553-9461-0d0afd18680a
(1 行)

lacl=# SELECT new_uuid();
               new_uuid
--------------------------------------
c60cab91-824f-47b9-8c5b-1876be35938d
(1 行)

lacl=# SELECT new_uuid();
               new_uuid
--------------------------------------
ab75fb58-1b70-a4eb-821f-cde29d86b232
(1 行)

lacl=#