一、在使用 date_format、from_unixtime、to_days、yearweek 函數(shù)時(shí)，Gbase 8s 數(shù)據(jù)庫不支持，可以使用創(chuàng)建 UDR 外部函數(shù)來實(shí)現(xiàn)

二、登錄命令控制臺(tái)或者使用 navicat 連接 Gbase 數(shù)據(jù)庫

這里使用 navicat ，點(diǎn)擊新增連接選擇 PostGreSql 驅(qū)動(dòng)，添加地址、賬號(hào)、密碼
連接數(shù)據(jù)庫后，選中目標(biāo)庫選中目標(biāo)模式，再點(diǎn)擊函數(shù)-新增函數(shù)執(zhí)行以下語句即可

注意：這里 選中 public 模式，使用 mss 用戶，自行修改函數(shù)中對(duì)應(yīng)的內(nèi)容（ 例如：FUNCTION “public”.“date_format”、OWNER TO “mss”）

1. date_format 函數(shù)

   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');
   

2. from_unixtime 函數(shù)

   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);
   

3. to_days 函數(shù)

   -- 參數(shù) 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";
   
   -- 參數(shù) 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());
   

4. yearweek 函數(shù)

   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";
   

   查詢語句：文章來源地址http://www.zghlxwxcb.cn/news/detail-662181.html

   select YEARWEEK(now());
   select YEARWEEK('2023-01-03 12');
   

到了這里，關(guān)于南大通用數(shù)據(jù)庫(Gbase 8s) 創(chuàng)建UDR外部函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！