一、在使用 date_format、from_unixtime、to_days、yearweek 函数时，Gbase 8s 数据库不支持，可以使用创建 UDR 外部函数来实现
二、登录命令控制台或者使用 navicat 连接 Gbase 数据库
这里使用 navicat ，点击新增连接选择 PostGreSql 驱动，添加地址、账号、密码
连接数据库后，选中目标库选中目标模式，再点击函数-新增函数执行以下语句即可
注意：这里 选中 public 模式，使用 mss 用户，自行修改函数中对应的内容（ 例如：FUNCTION “public”.“date_format”、OWNER TO “mss”）
-
date_format 函数
CREATE OR REPLACE FUNCTION "public"."date_format"("ctimestamp" timestamptz, "informate" varchar) RETURNS "pg_catalog"."varchar" AS $BODY$ -- Routine body goes here... DECLARE result_current_date varchar; BEGIN -- IF upper($1) = upper('YYYY-MM-DD') || upper($1) = upper('%Y-%M-%D') THEN -- SELECT to_char(now(),'YYYY-MM-DD') into result_current_date; -- END IF; -- -- -- IF upper($1) = upper('%Y-%M-%D %h:%m') || upper($1) = upper('%Y-%M-%D %h:%m') THEN -- SELECT to_char(now(),'YYYY-MM-DD HH:mm') into result_current_date; -- END IF; -- -- IF upper($1) = upper('%Y-%M-%D %h:%m:%s') || upper($1) = upper('%Y-%M-%D %h:%m:%s') THEN -- SELECT to_char(now(),'YYYY-MM-DD HH:mm:ss') into result_current_date; -- END IF; case upper($2) when upper('%Y') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY') into result_current_date; when upper('%Y-%M') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY-MM') into result_current_date; when upper('%Y-%M-%D') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY-MM-DD') into result_current_date; when upper('%Y-%M-%D %h') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY-MM-DD HH24') into result_current_date; when upper('%Y-%M-%D %h:%m') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY-MM-DD HH24:MI') into result_current_date; when upper('%Y-%M-%D %h:%m:%s') then SELECT to_char(smalldatetime_to_timestamp($1),'YYYY-MM-DD HH24:MI:ss') into result_current_date; when upper('%M') then SELECT to_char(smalldatetime_to_timestamp($1),'MM') into result_current_date; when upper('%M-%D') then SELECT to_char(smalldatetime_to_timestamp($1),'MM-DD') into result_current_date; when upper('%D') then SELECT to_char(smalldatetime_to_timestamp($1),'DD') into result_current_date; when upper('%h') then SELECT to_char(smalldatetime_to_timestamp($1),'HH24') into result_current_date; when upper('%h:%m') then SELECT to_char(smalldatetime_to_timestamp($1),'HH24:MI') into result_current_date; when upper('%m') then SELECT to_char(smalldatetime_to_timestamp($1),'MI') into result_current_date; when upper('%m:%s') then SELECT to_char(smalldatetime_to_timestamp($1),'MI:ss') into result_current_date; when upper('%s') then SELECT to_char(smalldatetime_to_timestamp($1),'ss') into result_current_date; when upper('%h:%m:%s') then SELECT to_char(smalldatetime_to_timestamp($1),'HH24:MI:ss') into result_current_date; else SELECT to_char(smalldatetime_to_timestamp($1),informate) into result_current_date; end case; RETURN result_current_date; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "public"."date_format"("ctimestamp" timestamptz, "informate" varchar) OWNER TO "mss";查询语句：
SELECT date_format(now(),'%Y-%M-%D %h:%m:%s'); -
from_unixtime 函数
CREATE OR REPLACE FUNCTION "public"."from_unixtime"("t" int8) RETURNS "pg_catalog"."timestamp" AS $BODY$ DECLARE result_current_date timestamp; BEGIN select TO_TIMESTAMP(t) into result_current_date; RETURN result_current_date; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "public"."from_unixtime"("t" int8) OWNER TO "mss";查询语句：
select from_unixtime(1692328679); -
to_days 函数
-- 参数 varchar类型 CREATE OR REPLACE FUNCTION "public"."to_days"("ctimestamp" varchar) RETURNS "pg_catalog"."int4" AS $BODY$ -- Routine body goes here... DECLARE result_current_date int4; BEGIN SELECT TIMESTAMPDIFF(day, '0001-01-01', $1) into result_current_date; RETURN result_current_date; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "public"."to_days"("ctimestamp" varchar) OWNER TO "mss"; -- 参数 timestamptz 类型 CREATE OR REPLACE FUNCTION "public"."to_days"("ctimestamp" timestamptz) RETURNS "pg_catalog"."int4" AS $BODY$ -- Routine body goes here... DECLARE result_current_date int4; BEGIN SELECT TIMESTAMPDIFF(day, '0001-01-01', $1) into result_current_date; RETURN result_current_date; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "public"."to_days"("ctimestamp" timestamptz) OWNER TO "mss";查询语句：
select to_days(now()); -
yearweek 函数文章来源：https://www.toymoban.com/news/detail-662181.html
CREATE OR REPLACE FUNCTION "public"."yearweek"("ctimestamp" timestamptz) RETURNS "pg_catalog"."int4" AS $BODY$ -- Routine body goes here... DECLARE week_n int4; year_n int4; BEGIN SELECT to_char(smalldatetime_to_timestamp($1),'YYYY') into year_n; SELECT trunc(1 + (smalldatetime_to_timestamp($1) - TRUNC(smalldatetime_to_timestamp($1), 'YEAR')) / 7) into week_n; RETURN ((year_n*100)+week_n); END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION "public"."yearweek"("ctimestamp" timestamptz) OWNER TO "mss";查询语句：文章来源地址https://www.toymoban.com/news/detail-662181.html
select YEARWEEK(now()); select YEARWEEK('2023-01-03 12');
到了这里，关于南大通用数据库(Gbase 8s) 创建UDR外部函数的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
