buildpostgres1c/ext.sql at main · KasperWPS/buildpostgres1c · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
CREATE OR REPLACE FUNCTION datediff(character varying, timestamp without time zone, timestamp without time zone) RETURNS integer AS
$BODY$
DECLARE
arg_mode alias for $1;
arg_d2 alias for $2;
arg_d1 alias for $3;
BEGIN
if arg_mode = 'SECOND' then
return date_part('epoch',arg_d1) - date_part('epoch',arg_d2);
elsif arg_mode = 'MINUTE' then
return ceil((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) / 60);
elsif arg_mode = 'HOUR' then
return ceil((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) /3600);
elsif arg_mode = 'DAY' then
return cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = 'WEEK' then
return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = 'MONTH' then
return 12 * (date_part('year',arg_d1) - date_part('year',arg_d2)) + date_part('month',arg_d1) - date_part('month',arg_d2);
elsif arg_mode = 'QUARTER' then
return 4 * (date_part('year',arg_d1) - date_part('year',arg_d2)) + date_part('quarter',arg_d1) - date_part('quarter',arg_d2);
elsif arg_mode = 'YEAR' then
return (date_part('year',arg_d1) - date_part('year',arg_d2));
end if;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE OR REPLACE FUNCTION public.datediff2(
    character varying,
    timestamp without time zone,
    timestamp without time zone)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS
$BODY$
    DECLARE
     arg_mode alias for $1;
     arg_d2 alias for $2;
     arg_d1 alias for $3;
    BEGIN
    if arg_mode = 'SECOND' then
     return date_part('epoch',arg_d1) - date_part('epoch',arg_d2) ;
    elsif arg_mode = 'MINUTE' then
     return trunc((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) / 60);
    elsif arg_mode = 'HOUR' then
     return trunc((date_part('epoch',arg_d1) - date_part('epoch',arg_d2)) /3600);
    elsif arg_mode = 'DAY' then
     return cast(arg_d1 as date) - cast(arg_d2 as date);
    elsif arg_mode = 'WEEK' then
            return trunc( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
    elsif arg_mode = 'MONTH' then
     return 12 * (date_part('year',arg_d1) - date_part('year',arg_d2))
          + date_part('month',arg_d1) - date_part('month',arg_d2);
    elsif arg_mode = 'QUARTER' then
     return 4 * (date_part('year',arg_d1) - date_part('year',arg_d2))
          + date_part('quarter',arg_d1) - date_part('quarter',arg_d2);
    elsif arg_mode = 'YEAR' then
     return (date_part('year',arg_d1) - date_part('year',arg_d2));
   end if;
    END

$BODY$;

create or replace function plpgsql_call_handler()
returns language_handler
as '$libdir/plpgsql', 'plpgsql_call_handler'
language 'c';