SQL backend engine scripts

There might be some additional backend engine SQL specific that you might need for initializing fields from the SQL side. Something that returns a UTC timestamp and something that returns a GUID/UUID. I mostly work with PostgreSQL so I can show the ones that I use. You can use them as examples to build your own for your backend. The internet might have additional examples available.

Engine: Mimer

Engine: MSSQL

Engine: MYSQL

Engine: PostgreSQL

These script examples are from one of my PostgreSQL application database.

define.ryb_sf_getelapsedtimeutc()

-- FUNCTION: define.ryb_sf_getelapsedtimeutc()
-- DROP FUNCTION define.ryb_sf_getelapsedtimeutc();
CREATE OR REPLACE FUNCTION define.ryb_sf_getelapsedtimeutc()
RETURNS bigint
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
begin RETURN ( SELECT (EXTRACT(EPOCH FROM (SELECT NOW() AT TIME ZONE 'UTC')) * 1000) );
end
$BODY$;
ALTER FUNCTION define.ryb_sf_getelapsedtimeutc() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_dba WITH GRANT OPTION;

define.ryb_sf_makeguid()

-- FUNCTION: define.ryb_sf_makeguid()
-- DROP FUNCTION define.ryb_sf_makeguid();
CREATE OR REPLACE FUNCTION define.ryb_sf_makeguid()
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}';
result text := '';
i integer := 0;
begin
for i in 1..16 loop
result := result || chars[ceil(35 * random())];
end loop;
return result;
end;
$BODY$;
ALTER FUNCTION define.ryb_sf_makeguid() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_dba WITH GRANT OPTION;

define.ryb_sf_random_string(integer)

-- FUNCTION: define.ryb_sf_random_string(integer)
-- DROP FUNCTION define.ryb_sf_random_string(integer);
CREATE OR REPLACE FUNCTION define.ryb_sf_random_string(integer)
RETURNS text
LANGUAGE 'sql'
COST 100
VOLATILE
AS $BODY$
SELECT array_to_string( ARRAY (
SELECT substring( '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
FROM ( ceil(random()*62))::int FOR 1 )
FROM generate_series(1, $1)
), '' )
$BODY$;
ALTER FUNCTION define.ryb_sf_random_string(integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_dba WITH GRANT OPTION;

Engine: SQLAnywhere