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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
CREATE FUNCTION return_void() RETURNS void AS $$ $$ LANGUAGE pljs;
SELECT return_void();
return_void
-------------
(1 row)
CREATE FUNCTION return_null() RETURNS text AS $$ return null; $$ LANGUAGE pljs;
SELECT r, r IS NULL AS isnull FROM return_null() AS r;
r | isnull
---+--------
| t
(1 row)
-- TYPE CONVERTIONS
CREATE FUNCTION int2_to_int4(x int2) RETURNS int4 AS $$ return x; $$ LANGUAGE pljs;
SELECT int2_to_int4(24::int2);
int2_to_int4
--------------
24
(1 row)
CREATE FUNCTION int4_to_int2(x int4) RETURNS int2 AS $$ return x; $$ LANGUAGE pljs;
SELECT int4_to_int2(42);
int4_to_int2
--------------
42
(1 row)
CREATE FUNCTION int4_to_int8(x int4) RETURNS int8 AS $$ return x; $$ LANGUAGE pljs;
SELECT int4_to_int8(48);
int4_to_int8
--------------
48
(1 row)
CREATE FUNCTION int8_to_int4(x int8) RETURNS int4 AS $$ return x; $$ LANGUAGE pljs;
SELECT int8_to_int4(84);
int8_to_int4
--------------
84
(1 row)
CREATE FUNCTION float8_to_numeric(x float8) RETURNS numeric AS $$ return x; $$ LANGUAGE pljs;
SELECT float8_to_numeric(1.5);
float8_to_numeric
-------------------
1.5
(1 row)
CREATE FUNCTION numeric_to_int8(x numeric) RETURNS int8 AS $$ return x; $$ LANGUAGE pljs;
SELECT numeric_to_int8(1234.56);
numeric_to_int8
-----------------
1234
(1 row)
CREATE FUNCTION int4_to_text(x int4) RETURNS text AS $$ return x; $$ LANGUAGE pljs;
SELECT int4_to_text(123);
int4_to_text
--------------
123
(1 row)
CREATE FUNCTION text_to_int4(x text) RETURNS int4 AS $$ return x; $$ LANGUAGE pljs;
SELECT text_to_int4('123');
text_to_int4
--------------
123
(1 row)
SELECT text_to_int4('abc');
text_to_int4
--------------
0
(1 row)
-- ARRAYS
CREATE FUNCTION return_array() RETURNS TEXT[] AS $$ return ["foo", "bar"]; $$LANGUAGE pljs;
SELECT return_array();
return_array
--------------
{foo,bar}
(1 row)
-- BigInt
-- a BigInt that will work on any value
CREATE OR REPLACE FUNCTION bigint_working(val BIGINT)
RETURNS BIGINT AS $$
return val - 1n;
$$ LANGUAGE pljs STABLE STRICT;
SELECT bigint_working(9223372036854775807);
bigint_working
---------------------
9223372036854775806
(1 row)
SELECT bigint_working(32);
bigint_working
----------------
31
(1 row)
-- a BigInt that will fail on any value
CREATE OR REPLACE FUNCTION bigint_failing(val BIGINT)
RETURNS BIGINT AS $$
return val - 1;
$$ LANGUAGE pljs STABLE STRICT;
SELECT bigint_failing(9223372036854775807);
ERROR: execution error
DETAIL: TypeError: cannot convert bigint to number
at bigint_failing (<function>:3:16)
-- BigInt as Numeric
CREATE OR REPLACE FUNCTION bigint_numeric(a INT8, b INT8)
RETURNS NUMERIC AS $$
return a ** b;
$$ LANGUAGE pljs STABLE STRICT;
SELECT bigint_numeric(20, 200);
bigint_numeric
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
160693804425899027554196209234116260252220299378279283530137600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(1 row)
-- ENUM type
CREATE TYPE status AS ENUM ('active', 'inactive', 'pending');
CREATE FUNCTION enum_echo(s status) returns status AS $$
return s;
$$ LANGUAGE pljs;
SELECT enum_echo('active');
enum_echo
-----------
active
(1 row)
-- Custom type
CREATE EXTENSION ltree;
CREATE FUNCTION ltree_echo(l ltree) RETURNS ltree AS $$
return l;
$$ LANGUAGE pljs;
SELECT ltree_echo('1.2.3'::ltree);
ltree_echo
------------
1.2.3
(1 row)
|
