如何将此T-SQL代码转换为PostgreSQL / plpgsql [英] How to convert this T-SQL code to PostgreSQL / plpgsql
问题描述
我需要将以下tsql函数代码转换成一个plpgsql函数,我完全不知道如何:
I need to convert the following tsql function code into a plpgsql function and I have absolutely no idea how:
BEGIN
DECLARE @StartDate DATETIME
DECLARE @ResultDate DATETIME
SET @StartDate = CONVERT(DATETIME, 0)
SET @ResultDate =
CASE @Type
WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
WHEN 1 THEN DATEADD(mi, CEILING(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
ELSE @Date
END
RETURN @ResultDate
这是全注:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetIntervalDate]
(
@Date DATETIME,
@Type INT,
@Interval INT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @StartDate DATETIME
DECLARE @ResultDate DATETIME
SET @StartDate = CONVERT(DATETIME, 0)
SET @ResultDate =
CASE @Type
WHEN 0 THEN DATEADD(mi, FLOOR(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
WHEN 1 THEN DATEADD(mi, CEILING(DATEDIFF(mi, @StartDate, @Date) / CAST(@Interval AS FLOAT)) * @Interval, @StartDate)
ELSE @Date
END
RETURN @ResultDate
END
推荐答案
你的函数到现代的nativeplpgsql:
I translated your function to modern "native" plpgsql:
CREATE OR REPLACE FUNCTION get_interval_date(_ts timestamp
,_type int
,_interval int)
RETURNS timestamp AS
$func$
BEGIN
CASE _type
WHEN 0 THEN
RETURN timestamp 'epoch'
+ floor((extract(EPOCH FROM _ts)) / _interval)
* _interval * interval '1s';
WHEN 1 THEN
RETURN timestamp 'epoch'
+ ceil((extract(EPOCH FROM _ts)) / _interval)
* _interval * interval '1s';
ELSE
RETURN _ts;
END CASE;
END
$func$ LANGUAGE plpgsql IMMUTABLE;
主要点
-
使用小写参数名称而不是引用混合大小写T -SQL样式参数语法
Use lower case parameter names instead of quoted mixed case T-SQL style parameter syntax.
将_interval的无意义转换删除为十进制(不更改结果)。
Remove pointless cast of _interval to decimal (doesn't change result).
删除未使用的
_mystamp
。> DECLARE 。
Remove therefore unused
DECLARE
.使用更适合
CASE
而不是IF
。不要引用语言名称
plpgsql
。它是一个标识符,而不是字符串;现在容忍,但可能会导致问题。Never quote the language name
plpgsql
. It's an identifier, not a string; tolerated for now, but may lead to problems.这篇关于如何将此T-SQL代码转换为PostgreSQL / plpgsql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!