\set ECHO none
SET
SET
SET
--
-- test built-in date type oracle compatibility functions
--
set behavior_compat_options='display_leading_zero';
SET
SELECT add_months ('2003-08-01', 3);
add_months
---------------------
2003-11-01 00:00:00
(1 row)
SELECT add_months ('2003-08-01', -3);
add_months
---------------------
2003-05-01 00:00:00
(1 row)
SELECT add_months ('2003-08-21', -3);
add_months
---------------------
2003-05-21 00:00:00
(1 row)
SELECT add_months ('2003-01-31', 1);
add_months
---------------------
2003-02-28 00:00:00
(1 row)
SELECT add_months ('2008-02-28', 1);
add_months
---------------------
2008-03-28 00:00:00
(1 row)
SELECT add_months ('2008-02-29', 1);
add_months
---------------------
2008-03-29 00:00:00
(1 row)
SELECT add_months ('2008-01-31', 12);
add_months
---------------------
2009-01-31 00:00:00
(1 row)
SELECT add_months ('2008-01-31', -12);
add_months
---------------------
2007-01-31 00:00:00
(1 row)
SELECT add_months ('2008-01-31', 95903);
add_months
---------------------
9999-12-31 00:00:00
(1 row)
SELECT add_months ('2008-01-31', -80640);
add_months
------------------------
4713-01-31 00:00:00 BC
(1 row)
SELECT add_months ('03-21-2008',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months ('21-MAR-2008',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months ('21-MAR-08',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months ('2008-MAR-21',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months ('March 21,2008',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months('03/21/2008',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months('20080321',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SELECT add_months('080321',3);
add_months
---------------------
2008-06-21 00:00:00
(1 row)
SET search_path TO oracle,"$user", public, pg_catalog;
WARNING: It is invalid to set pg_temp or pg_catalog behind other schemas in search path explicitly. The priority order is pg_temp, pg_catalog and other schemas.
SET
SELECT add_months ('2003-08-01 10:12:21', 3);
add_months
---------------------
2003-11-01 10:12:21
(1 row)
SELECT add_months ('2003-08-01 10:21:21', -3);
add_months
---------------------
2003-05-01 10:21:21
(1 row)
SELECT add_months ('2003-08-21 12:21:21', -3);
add_months
---------------------
2003-05-21 12:21:21
(1 row)
SELECT add_months ('2003-01-31 01:12:45', 1);
add_months
---------------------
2003-02-28 01:12:45
(1 row)
SELECT add_months ('2008-02-28 02:12:12', 1);
add_months
---------------------
2008-03-28 02:12:12
(1 row)
SELECT add_months ('2008-02-29 12:12:12', 1);
add_months
---------------------
2008-03-29 12:12:12
(1 row)
SELECT add_months ('2008-01-31 11:11:21', 12);
add_months
---------------------
2009-01-31 11:11:21
(1 row)
SELECT add_months ('2008-01-31 11:21:21', -12);
add_months
---------------------
2007-01-31 11:21:21
(1 row)
SELECT add_months ('2008-01-31 12:12:12', 95903);
add_months
---------------------
9999-12-31 12:12:12
(1 row)
SELECT add_months ('2008-01-31 11:32:12', -80640);
add_months
------------------------
4713-01-31 11:32:12 BC
(1 row)
SELECT add_months ('03-21-2008 08:12:22',3);
add_months
---------------------
2008-06-21 08:12:22
(1 row)
SELECT add_months ('21-MAR-2008 06:02:12',3);
add_months
---------------------
2008-06-21 06:02:12
(1 row)
SELECT add_months ('21-MAR-08 12:11:22',3);
add_months
---------------------
2008-06-21 12:11:22
(1 row)
SELECT add_months ('2008-MAR-21 11:32:43',3);
add_months
---------------------
2008-06-21 11:32:43
(1 row)
SELECT add_months ('March 21,2008 12:32:12',3);
add_months
---------------------
2008-06-21 12:32:12
(1 row)
SELECT add_months('03/21/2008 12:32:12',3);
add_months
---------------------
2008-06-21 12:32:12
(1 row)
SELECT add_months('20080321 123244',3);
add_months
---------------------
2008-06-21 12:32:44
(1 row)
SELECT add_months('080321 121212',3);
add_months
---------------------
2008-06-21 12:12:12
(1 row)
SET search_path TO default;
SET
SELECT last_day('1900-02-01');
last_day
---------------------
1900-02-28 00:00:00
(1 row)
SELECT last_day('2000-02-01');
last_day
---------------------
2000-02-29 00:00:00
(1 row)
SELECT last_day('2007-02-01');
last_day
---------------------
2007-02-28 00:00:00
(1 row)
SELECT last_day('2008-02-01');
last_day
---------------------
2008-02-29 00:00:00
(1 row)
SET search_path TO oracle,"$user", public, pg_catalog;
WARNING: It is invalid to set pg_temp or pg_catalog behind other schemas in search path explicitly. The priority order is pg_temp, pg_catalog and other schemas.
SET
SELECT last_day('1900-02-01 12:12:11');
last_day
---------------------
1900-02-28 12:12:11
(1 row)
SELECT last_day('2000-02-01 121143');
last_day
---------------------
2000-02-29 12:11:43
(1 row)
SELECT last_day('2007-02-01 12:21:33');
last_day
---------------------
2007-02-28 12:21:33
(1 row)
SELECT last_day('2008-02-01 121212');
last_day
---------------------
2008-02-29 12:12:12
(1 row)
SET search_path TO default;
SET
SELECT next_day ('2003-08-01', 'TUESDAY');
next_day
---------------------
2003-08-05 00:00:00
(1 row)
SELECT next_day ('2003-08-06', 'WEDNESDAY');
next_day
---------------------
2003-08-13 00:00:00
(1 row)
SELECT next_day ('2003-08-06', 'SUNDAY');
next_day
---------------------
2003-08-10 00:00:00
(1 row)
SELECT next_day ('2008-01-01', 'sun');
next_day
---------------------
2008-01-06 00:00:00
(1 row)
SELECT next_day ('2008-01-01', 'sun');
next_day
---------------------
2008-01-06 00:00:00
(1 row)
SELECT next_day ('2008-01-01', 1);
next_day
---------------------
2008-01-06 00:00:00
(1 row)
SELECT next_day ('2008-01-01', 7);
next_day
---------------------
2008-01-05 00:00:00
(1 row)
SET search_path TO oracle,"$user", public, pg_catalog;
WARNING: It is invalid to set pg_temp or pg_catalog behind other schemas in search path explicitly. The priority order is pg_temp, pg_catalog and other schemas.
SET
SELECT next_day ('2003-08-01 111211', 'TUESDAY');
next_day
---------------------
2003-08-05 11:12:11
(1 row)
SELECT next_day ('2003-08-06 10:11:43', 'WEDNESDAY');
next_day
---------------------
2003-08-13 10:11:43
(1 row)
SELECT next_day ('2003-08-06 11:21:21', 'SUNDAY');
next_day
---------------------
2003-08-10 11:21:21
(1 row)
SELECT next_day ('2008-01-01 111343', 'sun');
next_day
---------------------
2008-01-06 11:13:43
(1 row)
SELECT next_day ('2008-01-01 121212', 'sun');
next_day
---------------------
2008-01-06 12:12:12
(1 row)
SELECT next_day ('2008-01-01 111213', 1);
next_day
---------------------
2008-01-06 11:12:13
(1 row)
SELECT next_day ('2008-01-01 11:12:13', 7);
next_day
---------------------
2008-01-05 11:12:13
(1 row)
SET search_path TO default;
SET
SELECT months_between ('2007-02-28', '2007-04-30');
months_between
----------------
-2
(1 row)
SELECT months_between ('2008-01-31', '2008-02-29');
months_between
----------------
-1
(1 row)
SELECT months_between ('2008-02-29', '2008-03-31');
months_between
----------------
-1
(1 row)
SELECT months_between ('2008-02-29', '2008-04-30');
months_between
----------------
-2
(1 row)
SELECT trunc(months_between('21-feb-2008', '2008-02-29'));
trunc
-------
0
(1 row)
SET search_path TO oracle,"$user", public, pg_catalog;
WARNING: It is invalid to set pg_temp or pg_catalog behind other schemas in search path explicitly. The priority order is pg_temp, pg_catalog and other schemas.
SET
SELECT months_between ('2007-02-28 111111', '2007-04-30 112121');
months_between
----------------
-2
(1 row)
SELECT months_between ('2008-01-31 11:32:11', '2008-02-29 11:12:12');
months_between
----------------
-1
(1 row)
SELECT months_between ('2008-02-29 10:11:13', '2008-03-31 10:12:11');
months_between
----------------
-1
(1 row)
SELECT months_between ('2008-02-29 111111', '2008-04-30 12:12:12');
months_between
----------------
-2
(1 row)
SELECT trunc(months_between('21-feb-2008 12:11:11', '2008-02-29 11:11:11'));
trunc
-------
0
(1 row)
SET search_path TO default;
SET
select length('jmenuji se Pavel Stehule'),dbms_pipe.pack_message('jmenuji se Pavel Stehule');
length | pack_message
--------+--------------
24 |
(1 row)
select length('a bydlim ve Skalici'),dbms_pipe.pack_message('a bydlim ve Skalici');
length | pack_message
--------+--------------
19 |
(1 row)
select dbms_pipe.send_message('pavel',0,1);
send_message
--------------
0
(1 row)
select dbms_pipe.send_message('pavel',0,2);
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('pavel',0);
receive_message
-----------------
0
(1 row)
select '>>>>'||dbms_pipe.unpack_message_text()||'<<<<';
?column?
----------------------------------
>>>>jmenuji se Pavel Stehule<<<<
(1 row)
select '>>>>'||dbms_pipe.unpack_message_text()||'<<<<';
?column?
-----------------------------
>>>>a bydlim ve Skalici<<<<
(1 row)
select dbms_pipe.receive_message('pavel',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.purge('bob');
purge
-------
(1 row)
select dbms_pipe.reset_buffer();
reset_buffer
--------------
(1 row)
select dbms_pipe.pack_message('012345678901234+1');
pack_message
--------------
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
select dbms_pipe.pack_message('012345678901234+2');
pack_message
--------------
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
select dbms_pipe.pack_message('012345678901234+3');
pack_message
--------------
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
--------------------------------------------
select dbms_pipe.receive_message('bob',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.unpack_message_text();
unpack_message_text
---------------------
012345678901234+1
(1 row)
select dbms_pipe.receive_message('bob',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.unpack_message_text();
unpack_message_text
---------------------
012345678901234+2
(1 row)
select dbms_pipe.receive_message('bob',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.unpack_message_text();
unpack_message_text
---------------------
012345678901234+3
(1 row)
select dbms_pipe.unique_session_name() LIKE 'PG$PIPE$%';
?column?
----------
t
(1 row)
select dbms_pipe.pack_message('012345678901234-1');
pack_message
--------------
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('bob',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.unpack_message_text();
unpack_message_text
---------------------
012345678901234-1
(1 row)
select dbms_pipe.pack_message('012345678901234-2');
pack_message
--------------
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
select dbms_pipe.send_message('bob',0,10);
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('bob',0);
receive_message
-----------------
0
(1 row)
select dbms_pipe.unpack_message_text();
unpack_message_text
---------------------
012345678901234-2
(1 row)
select dbms_pipe.pack_message(TO_DATE('2006-10-11', 'YYYY-MM-DD'));
pack_message
--------------
(1 row)
select dbms_pipe.send_message('test_date');
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('test_date');
receive_message
-----------------
0
(1 row)
select dbms_pipe.next_item_type();
next_item_type
----------------
13
(1 row)
select dbms_pipe.unpack_message_date();
unpack_message_date
---------------------
2006-10-10 16:00:00
(1 row)
select dbms_pipe.pack_message(to_timestamp('2008-10-30 01:23:45', 'YYYY-MM-DD HH24:MI:SS'));
pack_message
--------------
(1 row)
select dbms_pipe.send_message('test_timestamp');
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('test_timestamp');
receive_message
-----------------
0
(1 row)
select dbms_pipe.next_item_type();
next_item_type
----------------
13
(1 row)
select to_char(dbms_pipe.unpack_message_timestamp(), 'YYYY-MM-DD HH24:MI:SS');
to_char
---------------------
2008-10-30 01:23:45
(1 row)
select dbms_pipe.pack_message(6262626262::numeric);
pack_message
--------------
(1 row)
select dbms_pipe.send_message('test_int');
send_message
--------------
0
(1 row)
select dbms_pipe.receive_message('test_int');
receive_message
-----------------
0
(1 row)
select dbms_pipe.next_item_type();
next_item_type
----------------
9
(1 row)
select dbms_pipe.unpack_message_number();
unpack_message_number
-----------------------
6262626262
(1 row)
select dbms_pipe.purge('bob');
purge
-------
(1 row)
select dbms_pipe.reset_buffer();
reset_buffer
--------------
(1 row)
select name, items, "limit", private, owner from dbms_pipe.db_pipes where name = 'bob';
name | items | limit | private | owner
------+-------+-------+---------+-------
(0 rows)
select PLVstr.betwn('Harry and Sally are very happy', 7, 9);
betwn
-------
and
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 7, 9, FALSE);
betwn
-------
n
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', -3, -1);
betwn
-------
ppy
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry');
betwn
-------
arry
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry', 1,1,FALSE,FALSE);
betwn
-------
r
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry', 2,1,TRUE,FALSE);
betwn
--------------------
and Sally are very
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'y', 2,1);
betwn
-----------
and Sally
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'a', 2, 2);
betwn
-------------
and Sally a
(1 row)
select PLVstr.betwn('Harry and Sally are very happy', 'a', 'a', 2, 3, FALSE,FALSE);
betwn
---------------------
nd Sally are very h
(1 row)
select plvsubst.string('My name is %s %s.', ARRAY['Pavel','Stěhule']);
string
---------------------------
My name is Pavel Stěhule.
(1 row)
select plvsubst.string('My name is % %.', ARRAY['Pavel','Stěhule'], '%');
string
---------------------------
My name is Pavel Stěhule.
(1 row)
select plvsubst.string('My name is %s.', ARRAY['Stěhule']);
string
---------------------
My name is Stěhule.
(1 row)
select plvsubst.string('My name is %s %s.', 'Pavel,Stěhule');
string
---------------------------
My name is Pavel Stěhule.
(1 row)
select plvsubst.string('My name is %s %s.', 'Pavel|Stěhule','|');
string
---------------------------
My name is Pavel Stěhule.
(1 row)
select plvsubst.string('My name is %s.', 'Stěhule');
string
---------------------
My name is Stěhule.
(1 row)
select plvsubst.string('My name is %s.', '');
string
--------
(1 row)
select plvsubst.string('My name is empty.', '');
string
--------
(1 row)
set time zone 'US/Pacific';
SET
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','YEAR') = '2004-01-01 00:00:00-08';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','Q') = '2004-10-01 00:00:00-07';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','MONTH') = '2004-10-01 00:00:00-07';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','DDD') = '2004-10-19 00:00:00-07';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','DAY') = '2004-10-19 00:00:00-07';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','HH') = '2004-10-19 01:00:00-07';
?column?
----------
t
(1 row)
select trunc(TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02','MI') = '2004-10-19 01:23:00-07';
?column?
----------
t
(1 row)
SET search_path TO default;
SET
select instr('Tech on the net', 'e') =2;
?column?
----------
t
(1 row)
select instr('Tech on the net', 'e', 1, 1) = 2;
?column?
----------
t
(1 row)
select instr('Tech on the net', 'e', 1, 2) = 11;
?column?
----------
t
(1 row)
select instr('Tech on the net', 'e', 1, 3) = 14;
?column?
----------
t
(1 row)
select instr('Tech on the net', 'e', -3, 2) = 2;
?column?
----------
t
(1 row)
select instr('abc', NULL) IS NULL;
?column?
----------
t
(1 row)
select 1 = instr('abc', '');
?column?
----------
(1 row)
select 1 = instr('abc', 'a');
?column?
----------
t
(1 row)
select 3 = instr('abc', 'c');
?column?
----------
t
(1 row)
select 0 = instr('abc', 'z');
?column?
----------
t
(1 row)
select 1 = instr('abcabcabc', 'abca', 1);
?column?
----------
t
(1 row)
select 4 = instr('abcabcabc', 'abca', 2);
?column?
----------
t
(1 row)
select 0 = instr('abcabcabc', 'abca', 7);
?column?
----------
t
(1 row)
select 0 = instr('abcabcabc', 'abca', 9);
?column?
----------
t
(1 row)
select 4 = instr('abcabcabc', 'abca', -1);
?column?
----------
t
(1 row)
select 1 = instr('abcabcabc', 'abca', -8);
?column?
----------
t
(1 row)
select 1 = instr('abcabcabc', 'abca', -9);
?column?
----------
t
(1 row)
select 0 = instr('abcabcabc', 'abca', -10);
?column?
----------
t
(1 row)
select 1 = instr('abcabcabc', 'abca', 1, 1);
?column?
----------
t
(1 row)
select 4 = instr('abcabcabc', 'abca', 1, 2);
?column?
----------
t
(1 row)
select 0 = instr('abcabcabc', 'abca', 1, 3);
?column?
----------
t
(1 row)
select oracle.substr('This is a test', 6, 2) = 'is';
?column?
----------
t
(1 row)
select oracle.substr('This is a test', 6) = 'is a test';
?column?
----------
t
(1 row)
select oracle.substr('TechOnTheNet', 1, 4) = 'Tech';
?column?
----------
t
(1 row)
select oracle.substr('TechOnTheNet', -3, 3) = 'Net';
?column?
----------
t
(1 row)
select oracle.substr('TechOnTheNet', -6, 3) = 'The';
?column?
----------
t
(1 row)
select oracle.substr('TechOnTheNet', -8, 2) = 'On';
?column?
----------
t
(1 row)
select oracle.substr('TechOnTheNet', -8, 0) = '';
?column?
----------
(1 row)
select oracle.substr('TechOnTheNet', -8, -1) = '';
?column?
----------
(1 row)
select oracle.substr(1234567,3.6::smallint)='4567';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::int)='4567';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::bigint)='4567';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::numeric)='34567';
?column?
----------
t
(1 row)
select oracle.substr(1234567,-1)='7';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::smallint,2.6)='45';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::smallint,2.6::smallint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::smallint,2.6::int)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::smallint,2.6::bigint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::smallint,2.6::numeric)='45';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::int,2.6::smallint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::int,2.6::int)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::int,2.6::bigint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::int,2.6::numeric)='45';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::bigint,2.6::smallint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::bigint,2.6::int)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::bigint,2.6::bigint)='456';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::bigint,2.6::numeric)='45';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::numeric,2.6::smallint)='345';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::numeric,2.6::int)='345';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::numeric,2.6::bigint)='345';
?column?
----------
t
(1 row)
select oracle.substr(1234567,3.6::numeric,2.6::numeric)='34';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.6::smallint)='def';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.6::int)='def';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.6::bigint)='def';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.6::numeric)='cdef';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.5::int,3.5::int)='def';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.5::numeric,3.5::numeric)='cde';
?column?
----------
t
(1 row)
select oracle.substr('abcdef'::varchar,3.5::numeric,3.5::int)='cdef';
?column?
----------
t
(1 row)
select concat('Tech on', ' the Net') = 'Tech on the Net';
?column?
----------
t
(1 row)
select concat('a', 'b') = 'ab';
?column?
----------
t
(1 row)
select concat('a', NULL) = 'a';
?column?
----------
t
(1 row)
select concat(NULL, 'b') = 'b';
?column?
----------
t
(1 row)
select concat('a', 2) = 'a2';
?column?
----------
t
(1 row)
select concat(1, 'b') = '1b';
?column?
----------
t
(1 row)
select concat(1, 2) = '12';
?column?
----------
t
(1 row)
select concat(1, NULL) = '1';
?column?
----------
t
(1 row)
select concat(NULL, 2) = '2';
?column?
----------
t
(1 row)
select nvl('A'::text, 'B');
nvl
-----
A
(1 row)
select nvl(NULL::text, 'B');
nvl
-----
B
(1 row)
select nvl(NULL::text, NULL);
nvl
-----
(1 row)
select nvl(1, 2);
nvl
-----
1
(1 row)
select nvl(NULL, 2);
nvl
-----
2
(1 row)
select nvl2('A'::text, 'B', 'C');
nvl2
------
B
(1 row)
select nvl2(NULL::text, 'B', 'C');
nvl2
------
C
(1 row)
select nvl2('A'::text, NULL, 'C');
nvl2
------
(1 row)
select nvl2(NULL::text, 'B', NULL);
nvl2
------
(1 row)
select nvl2(1, 2, 3);
nvl2
------
2
(1 row)
select nvl2(NULL, 2, 3);
nvl2
------
3
(1 row)
select lnnvl(true);
lnnvl
-------
f
(1 row)
select lnnvl(false);
lnnvl
-------
t
(1 row)
select lnnvl(NULL);
lnnvl
-------
t
(1 row)
select public.decode(1, 1, 100, 2, 200);
decode
--------
100
(1 row)
select public.decode(2, 1, 100, 2, 200);
decode
--------
200
(1 row)
select public.decode(3, 1, 100, 2, 200);
decode
--------
(1 row)
select public.decode(3, 1, 100, 2, 200, 300);
decode
--------
300
(1 row)
select public.decode(NULL, 1, 100, NULL, 200, 300);
decode
--------
200
(1 row)
select public.decode('1'::text, '1', 100, '2', 200);
decode
--------
100
(1 row)
select public.decode(2, 1, 'ABC', 2, 'DEF');
decode
--------
DEF
(1 row)
select public.decode('2009-02-05'::date, '2009-02-05', 'ok');
decode
--------
ok
(1 row)
select public.decode('2009-02-05 01:02:03'::timestamp, '2009-02-05 01:02:03', 'ok');
decode
--------
ok
(1 row)
-- For type 'bpchar'
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('c'::bpchar, 'a'::bpchar,'postgres'::bpchar);
decode
--------
(1 row)
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar,'default value'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('c', 'a'::bpchar,'postgres'::bpchar,'default value'::bpchar);
decode
---------------
default value
(1 row)
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('d'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar);
decode
--------
(1 row)
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar,'default value'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('d'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar,'default value'::bpchar);
decode
---------------
default value
(1 row)
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar, 'c'::bpchar, 'system'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('d'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar, 'c'::bpchar, 'system'::bpchar);
decode
--------
(1 row)
select public.decode('a'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar, 'c'::bpchar, 'system'::bpchar,'default value'::bpchar);
decode
----------
postgres
(1 row)
select public.decode('d'::bpchar, 'a'::bpchar,'postgres'::bpchar,'b'::bpchar,'database'::bpchar, 'c'::bpchar, 'system'::bpchar,'default value'::bpchar);
decode
---------------
default value
(1 row)
select public.decode(NULL, 'a'::bpchar, 'postgres'::bpchar, NULL,'database'::bpchar);
decode
----------
database
(1 row)
select public.decode(NULL, 'a'::bpchar, 'postgres'::bpchar, 'b'::bpchar,'database'::bpchar);
decode
--------
(1 row)
select public.decode(NULL, 'a'::bpchar, 'postgres'::bpchar, NULL,'database'::bpchar,'default value'::bpchar);
decode
----------
database
(1 row)
select public.decode(NULL, 'a'::bpchar, 'postgres'::bpchar, 'b'::bpchar,'database'::bpchar,'default value'::bpchar);
decode
---------------
default value
(1 row)
-- For type 'bigint'
select public.decode(2147483651::bigint, 2147483650::bigint,2147483650::bigint);
decode
--------
(1 row)
select public.decode(2147483653::bigint, 2147483651::bigint,2147483650::bigint);
decode
--------
(1 row)
select public.decode(2147483653::bigint, 2147483651::bigint,2147483650::bigint,9999999999::bigint);
decode
------------
9999999999
(1 row)
select public.decode(2147483653::bigint, 2147483651::bigint,2147483650::bigint,9999999999::bigint);
decode
------------
9999999999
(1 row)
select public.decode(2147483651::bigint, 2147483651::bigint,2147483650::bigint,2147483652::bigint,2147483651::bigint);
decode
------------
2147483650
(1 row)
select public.decode(2147483654::bigint, 2147483651::bigint,2147483650::bigint,2147483652::bigint,2147483651::bigint);
decode
--------
(1 row)
select public.decode(2147483651::bigint, 2147483651::bigint,2147483650::bigint,2147483652::bigint,2147483651::bigint,9999999999::bigint);
decode
------------
2147483650
(1 row)
select public.decode(2147483654::bigint, 2147483651::bigint,2147483650::bigint,2147483652::bigint,2147483651::bigint,9999999999::bigint);
decode
------------
9999999999
(1 row)
select public.decode(2147483651::bigint, 2147483651::bigint,2147483650::bigint, 2147483652::bigint,2147483651::bigint, 2147483653::bigint, 2147483652::bigint);
decode
------------
2147483650
(1 row)
select public.decode(2147483654::bigint, 2147483651::bigint,2147483650::bigint, 2147483652::bigint,2147483651::bigint, 2147483653::bigint, 2147483652::bigint);
decode
--------
(1 row)
select public.decode(2147483651::bigint, 2147483651::bigint,2147483650::bigint, 2147483652::bigint,2147483651::bigint, 2147483653::bigint, 2147483652::bigint,9999999999::bigint);
decode
------------
2147483650
(1 row)
select public.decode(2147483654::bigint, 2147483651::bigint,2147483650::bigint, 2147483652::bigint,2147483651::bigint, 2147483653::bigint, 2147483652::bigint,9999999999::bigint);
decode
------------
9999999999
(1 row)
select public.decode(NULL, 2147483651::bigint, 2147483650::bigint, NULL,2147483651::bigint);
decode
------------
2147483651
(1 row)
select public.decode(NULL, 2147483651::bigint, 2147483650::bigint, 2147483652::bigint,2147483651::bigint);
decode
--------
(1 row)
select public.decode(NULL, 2147483651::bigint, 2147483650::bigint, NULL,2147483651::bigint,9999999999::bigint);
decode
------------
2147483651
(1 row)
select public.decode(NULL, 2147483651::bigint, 2147483650::bigint, 2147483652::bigint,2147483651::bigint,9999999999::bigint);
decode
------------
9999999999
(1 row)
-- For type 'numeric'
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.003::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4));
decode
--------
(1 row)
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.003::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
999999.9999
(1 row)
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.004::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4));
decode
--------
(1 row)
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.004::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
999999.9999
(1 row)
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4), 12.003::numeric(5,3), 214748.3652::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.004::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4), 12.003::numeric(5,3), 214748.3652::numeric(10,4));
decode
--------
(1 row)
select public.decode(12.001::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4), 12.003::numeric(5,3), 214748.3652::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
214748.3650
(1 row)
select public.decode(12.004::numeric(5,3), 12.001::numeric(5,3),214748.3650::numeric(10,4),12.002::numeric(5,3),214748.3651::numeric(10,4), 12.003::numeric(5,3), 214748.3652::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
999999.9999
(1 row)
select public.decode(NULL, 12.001::numeric(5,3), 214748.3650::numeric(10,4), NULL,214748.3651::numeric(10,4));
decode
-------------
214748.3651
(1 row)
select public.decode(NULL, 12.001::numeric(5,3), 214748.3650::numeric(10,4), 12.002::numeric(5,3),214748.3651::numeric(10,4));
decode
--------
(1 row)
select public.decode(NULL, 12.001::numeric(5,3), 214748.3650::numeric(10,4), NULL,214748.3651::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
214748.3651
(1 row)
select public.decode(NULL, 12.001::numeric(5,3), 214748.3650::numeric(10,4), 12.002::numeric(5,3),214748.3651::numeric(10,4),999999.9999::numeric(10,4));
decode
-------------
999999.9999
(1 row)
--For type 'date'
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-03'::date, '2020-01-01'::date,'2012-12-20'::date);
decode
--------
(1 row)
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date,'2012-12-21'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-03'::date, '2020-01-01'::date,'2012-12-20'::date,'2012-12-21'::date);
decode
---------------------
2012-12-21 00:00:00
(1 row)
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-04'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date);
decode
--------
(1 row)
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date,'2012-12-31'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-04'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date,'2012-12-31'::date);
decode
---------------------
2012-12-31 00:00:00
(1 row)
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date, '2020-01-03'::date, '2012-12-31'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-04'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date, '2020-01-03'::date, '2012-12-31'::date);
decode
--------
(1 row)
select public.decode('2020-01-01'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date, '2020-01-03'::date, '2012-12-31'::date,'2013-01-01'::date);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-04'::date, '2020-01-01'::date,'2012-12-20'::date,'2020-01-02'::date,'2012-12-21'::date, '2020-01-03'::date, '2012-12-31'::date,'2013-01-01'::date);
decode
---------------------
2013-01-01 00:00:00
(1 row)
select public.decode(NULL, '2020-01-01'::date, '2012-12-20'::date, NULL,'2012-12-21'::date);
decode
---------------------
2012-12-21 00:00:00
(1 row)
select public.decode(NULL, '2020-01-01'::date, '2012-12-20'::date, '2020-01-02'::date,'2012-12-21'::date);
decode
--------
(1 row)
select public.decode(NULL, '2020-01-01'::date, '2012-12-20'::date, NULL,'2012-12-21'::date,'2012-12-31'::date);
decode
---------------------
2012-12-21 00:00:00
(1 row)
select public.decode(NULL, '2020-01-01'::date, '2012-12-20'::date, '2020-01-02'::date,'2012-12-21'::date,'2012-12-31'::date);
decode
---------------------
2012-12-31 00:00:00
(1 row)
-- For type 'time'
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:03'::time, '01:00:01'::time,'09:00:00'::time);
decode
--------
(1 row)
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time,'00:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:03'::time, '01:00:01'::time,'09:00:00'::time,'00:00:00'::time);
decode
----------
00:00:00
(1 row)
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:04'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time);
decode
--------
(1 row)
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time,'00:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:04'::time, '01:00:01'::time,'09:00:00'::time,'01:00:01'::time,'12:00:00'::time,'00:00:00'::time);
decode
----------
00:00:00
(1 row)
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time, '01:00:03'::time, '15:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:04'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time, '01:00:03'::time, '15:00:00'::time);
decode
--------
(1 row)
select public.decode('01:00:01'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time, '01:00:03'::time, '15:00:00'::time,'00:00:00'::time);
decode
----------
09:00:00
(1 row)
select public.decode('01:00:04'::time, '01:00:01'::time,'09:00:00'::time,'01:00:02'::time,'12:00:00'::time, '01:00:03'::time, '15:00:00'::time,'00:00:00'::time);
decode
----------
00:00:00
(1 row)
select public.decode(NULL, '01:00:01'::time, '09:00:00'::time, NULL,'12:00:00'::time);
decode
----------
12:00:00
(1 row)
select public.decode(NULL, '01:00:01'::time, '09:00:00'::time, '01:00:02'::time,'12:00:00'::time);
decode
--------
(1 row)
select public.decode(NULL, '01:00:01'::time, '09:00:00'::time, NULL,'12:00:00'::time,'00:00:00'::time);
decode
----------
12:00:00
(1 row)
select public.decode(NULL, '01:00:01'::time, '09:00:00'::time, '01:00:02'::time,'12:00:00'::time,'00:00:00'::time);
decode
----------
00:00:00
(1 row)
-- For type 'timestamp'
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-03 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-03 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-04 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-04 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp, '2020-01-03 01:00:01'::timestamp, '2012-12-20 15:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-04 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp, '2020-01-03 01:00:01'::timestamp, '2012-12-20 15:00:00'::timestamp);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp, '2020-01-03 01:00:01'::timestamp, '2012-12-20 15:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 09:00:00
(1 row)
select public.decode('2020-01-04 01:00:01'::timestamp, '2020-01-01 01:00:01'::timestamp,'2012-12-20 09:00:00'::timestamp,'2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp, '2020-01-03 01:00:01'::timestamp, '2012-12-20 15:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 00:00:00
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01'::timestamp, '2012-12-20 09:00:00'::timestamp, NULL,'2012-12-20 12:00:00'::timestamp);
decode
---------------------
2012-12-20 12:00:00
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01'::timestamp, '2012-12-20 09:00:00'::timestamp, '2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp);
decode
--------
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01'::timestamp, '2012-12-20 09:00:00'::timestamp, NULL,'2012-12-20 12:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 12:00:00
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01'::timestamp, '2012-12-20 09:00:00'::timestamp, '2020-01-02 01:00:01'::timestamp,'2012-12-20 12:00:00'::timestamp,'2012-12-20 00:00:00'::timestamp);
decode
---------------------
2012-12-20 00:00:00
(1 row)
-- For type 'timestamptz'
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode('2020-01-03 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode('2020-01-03 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 00:00:00-08
(1 row)
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode('2020-01-04 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode('2020-01-04 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 00:00:00-08
(1 row)
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz, '2020-01-03 01:00:01-08'::timestamptz, '2012-12-20 15:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode('2020-01-04 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz, '2020-01-03 01:00:01-08'::timestamptz, '2012-12-20 15:00:00-08'::timestamptz);
decode
--------
(1 row)
select public.decode('2020-01-01 01:00:01-08'::timestamptz, '2020-01-01 01:00:01-08'::timestamptz,'2012-12-20 09:00:00-08'::timestamptz,'2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz, '2020-01-03 01:00:01-08'::timestamptz, '2012-12-20 15:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 09:00:00-08
(1 row)
select public.decode(4, 1,'2012-12-20 09:00:00-08'::timestamptz,2,'2012-12-20 12:00:00-08'::timestamptz, 3, '2012-12-20 15:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 00:00:00-08
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01-08'::timestamptz, '2012-12-20 09:00:00-08'::timestamptz, NULL,'2012-12-20 12:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 12:00:00-08
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01-08'::timestamptz, '2012-12-20 09:00:00-08'::timestamptz, '2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz);
decode
--------
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01-08'::timestamptz, '2012-12-20 09:00:00-08'::timestamptz, NULL,'2012-12-20 12:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 12:00:00-08
(1 row)
select public.decode(NULL, '2020-01-01 01:00:01-08'::timestamptz, '2012-12-20 09:00:00-08'::timestamptz, '2020-01-02 01:00:01-08'::timestamptz,'2012-12-20 12:00:00-08'::timestamptz,'2012-12-20 00:00:00-08'::timestamptz);
decode
------------------------
2012-12-20 00:00:00-08
(1 row)
--Test case to check if decode accepts other expressions as a key
CREATE OR REPLACE FUNCTION five() RETURNS integer AS $$
BEGIN
RETURN 5;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
select public.decode(five(), 1, 'one', 2, 'two', 5, 'five');
decode
--------
five
(1 row)
DROP FUNCTION five();
DROP FUNCTION
-- Test case to check duplicate keys in search list
select public.decode(1, 1, 'one', 2, 'two', 1, 'one-again') = 'one';
?column?
----------
t
(1 row)
/* Test case to check explicit type casting of keys in search list in
* case of ambiguous key (1st argument) provided.
*/
-- 1) succeed and return 'result-1'
select public.decode('2012-01-01', '2012-01-01'::date,'result-1','2012-01-02', 'result-2');
decode
----------
result-1
(1 row)
select public.decode('2012-01-01', '2012-01-01', 'result-1', '2012-02-01'::date, 'result-2');
decode
----------
result-1
(1 row)
select PLVstr.rvrs ('Jumping Jack Flash') ='hsalF kcaJ gnipmuJ';
?column?
----------
t
(1 row)
select PLVstr.rvrs ('Jumping Jack Flash', 9) = 'hsalF kcaJ';
?column?
----------
t
(1 row)
select PLVstr.rvrs ('Jumping Jack Flash', 4, 6) = 'nip';
?column?
----------
t
(1 row)
select PLVstr.rvrs (NULL, 10, 20);
rvrs
------
(1 row)
select PLVstr.rvrs ('alphabet', -2, -5);
rvrs
------
ebah
(1 row)
select PLVstr.rvrs ('alphabet', -2);
rvrs
---------
ebahpla
(1 row)
select PLVstr.rvrs ('alphabet', 2, 200);
rvrs
---------
tebahpl
(1 row)
select PLVstr.rvrs ('alphabet', 20, 200);
rvrs
------
(1 row)
select PLVstr.lstrip ('*val1|val2|val3|*', '*') = 'val1|val2|val3|*';
?column?
----------
t
(1 row)
select PLVstr.lstrip (',,,val1,val2,val3,', ',', 3)= 'val1,val2,val3,';
?column?
----------
t
(1 row)
select PLVstr.lstrip ('WHERE WHITE = ''FRONT'' AND COMP# = 1500', 'WHERE ') = 'WHITE = ''FRONT'' AND COMP# = 1500';
?column?
----------
t
(1 row)
select plvstr.left('Příliš žluťoučký kůň',4) = pg_catalog.substr('Příl', 1, 4);
?column?
----------
t
(1 row)
select pos,token from plvlex.tokens('select * from a.b.c join d ON x=y', true, true);
pos | token
-----+--------
0 | select
7 | *
9 | from
14 | a.b.c
20 | join
25 | d
27 | on
30 | x
31 | =
32 | y
(10 rows)
SET lc_numeric TO 'C';
SET
select to_char(22);
to_char
---------
22
(1 row)
select to_char(99::smallint);
to_char
---------
99
(1 row)
select to_char(-44444);
to_char
---------
-44444
(1 row)
select to_char(1234567890123456::bigint);
to_char
------------------
1234567890123456
(1 row)
select to_char(123.456::real);
to_char
---------
123.456
(1 row)
select to_char(1234.5678::double precision);
to_char
-----------
1234.5678
(1 row)
select to_char(12345678901234567890::numeric);
to_char
----------------------
12345678901234567890
(1 row)
select to_char(1234567890.12345);
to_char
------------------
1234567890.12345
(1 row)
select to_char('4.00'::numeric);
to_char
---------
4.00
(1 row)
select to_char('4.0010'::numeric);
to_char
---------
4.0010
(1 row)
SELECT to_number('123'::text);
to_number
-----------
123
(1 row)
SELECT to_number('123.456'::text);
to_number
-----------
123.456
(1 row)
SELECT to_number(123);
to_number
-----------
123
(1 row)
SELECT to_number(123::smallint);
to_number
-----------
123
(1 row)
SELECT to_number(123::int);
to_number
-----------
123
(1 row)
SELECT to_number(123::bigint);
to_number
-----------
123
(1 row)
SELECT to_number(123::numeric);
to_number
-----------
123
(1 row)
SELECT to_number(123.456);
to_number
-----------
123.456
(1 row)
SELECT to_number(1210.73, 9999.99);
to_number
-----------
1210.73
(1 row)
SELECT to_number(1210::smallint, 9999::smallint);
to_number
-----------
1210
(1 row)
SELECT to_number(1210::int, 9999::int);
to_number
-----------
1210
(1 row)
SELECT to_number(1210::bigint, 9999::bigint);
to_number
-----------
1210
(1 row)
SELECT to_number(1210.73::numeric, 9999.99::numeric);
to_number
-----------
1210.73
(1 row)
SELECT bitand(5,1), bitand(5,2), bitand(5,4);
bitand | bitand | bitand
--------+--------+--------
1 | 0 | 4
(1 row)
SELECT sinh(1.570796)::numeric(10, 8), cosh(1.570796)::numeric(10, 8), tanh(4)::numeric(10, 8);
sinh | cosh | tanh
------------+------------+------------
2.30129808 | 2.50917773 | 0.99932930
(1 row)
SELECT nanvl(12345, 1), nanvl('NaN', 1);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float4, 1), nanvl('NaN'::float4, 1);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float8, 1), nanvl('NaN'::float8, 1);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::numeric, 1), nanvl('NaN'::numeric, 1);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345, '1'::varchar), nanvl('NaN', 1::varchar);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float4, '1'::varchar), nanvl('NaN'::float4, '1'::varchar);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float8, '1'::varchar), nanvl('NaN'::float8, '1'::varchar);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::numeric, '1'::varchar), nanvl('NaN'::numeric, '1'::varchar);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345, '1'::char), nanvl('NaN', 1::char);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float4, '1'::char), nanvl('NaN'::float4, '1'::char);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::float8, '1'::char), nanvl('NaN'::float8, '1'::char);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
SELECT nanvl(12345::numeric, '1'::char), nanvl('NaN'::numeric, '1'::char);
nanvl | nanvl
-------+-------
12345 | 1
(1 row)
select dbms_assert.enquote_literal('some text '' some text');
enquote_literal
--------------------------
'some text '' some text'
(1 row)
select dbms_assert.enquote_name('''"AAA');
enquote_name
--------------
"'""aaa"
(1 row)
select dbms_assert.enquote_name('''"AAA', false);
enquote_name
--------------
"'""AAA"
(1 row)
select dbms_assert.noop('some string');
noop
-------------
some string
(1 row)
select dbms_assert.qualified_sql_name('aaa.bbb.ccc."aaaa""aaa"');
qualified_sql_name
-------------------------
aaa.bbb.ccc."aaaa""aaa"
(1 row)
select dbms_assert.qualified_sql_name('aaa.bbb.cc%c."aaaa""aaa"');
ERROR: string is not qualified SQL name
CONTEXT: referenced column: qualified_sql_name
select dbms_assert.schema_name('dbms_assert');
schema_name
-------------
dbms_assert
(1 row)
select dbms_assert.schema_name('jabadabado');
ERROR: invalid schema name
CONTEXT: referenced column: schema_name
select dbms_assert.simple_sql_name('"Aaa dghh shsh"');
simple_sql_name
-----------------
"Aaa dghh shsh"
(1 row)
select dbms_assert.simple_sql_name('ajajaj -- ajaj');
ERROR: string is not simple SQL name
CONTEXT: referenced column: simple_sql_name
select dbms_assert.object_name('pg_catalog.pg_class');
object_name
---------------------
pg_catalog.pg_class
(1 row)
select dbms_assert.object_name('dbms_assert.fooo');
ERROR: invalid object name
CONTEXT: referenced column: object_name
select dbms_assert.enquote_literal(NULL);
enquote_literal
-----------------
(1 row)
select dbms_assert.enquote_name(NULL);
enquote_name
--------------
(1 row)
select dbms_assert.enquote_name(NULL, false);
enquote_name
--------------
(1 row)
select dbms_assert.noop(NULL);
noop
------
(1 row)
select dbms_assert.qualified_sql_name(NULL);
ERROR: string is not qualified SQL name
CONTEXT: referenced column: qualified_sql_name
select dbms_assert.qualified_sql_name(NULL);
ERROR: string is not qualified SQL name
CONTEXT: referenced column: qualified_sql_name
select dbms_assert.schema_name(NULL);
ERROR: invalid schema name
CONTEXT: referenced column: schema_name
select dbms_assert.schema_name(NULL);
ERROR: invalid schema name
CONTEXT: referenced column: schema_name
select dbms_assert.simple_sql_name(NULL);
ERROR: string is not simple SQL name
CONTEXT: referenced column: simple_sql_name
select dbms_assert.simple_sql_name(NULL);
ERROR: string is not simple SQL name
CONTEXT: referenced column: simple_sql_name
select dbms_assert.object_name(NULL);
ERROR: invalid object name
CONTEXT: referenced column: object_name
select dbms_assert.object_name(NULL);
ERROR: invalid object name
CONTEXT: referenced column: object_name
select plunit.assert_true(NULL);
ERROR: plunit.assert_true exception
DETAIL: Plunit.assertation fails (assert_true).
CONTEXT: referenced column: assert_true
select plunit.assert_true(1 = 2);
ERROR: plunit.assert_true exception
DETAIL: Plunit.assertation fails (assert_true).
CONTEXT: referenced column: assert_true
select plunit.assert_true(1 = 2, 'one is not two');
ERROR: one is not two
DETAIL: Plunit.assertation fails (assert_true).
CONTEXT: referenced column: assert_true
select plunit.assert_true(1 = 1);
assert_true
-------------
(1 row)
select plunit.assert_false(1 = 1);
ERROR: plunit.assert_false exception
DETAIL: Plunit.assertation fails (assert_false).
CONTEXT: referenced column: assert_false
select plunit.assert_false(1 = 1, 'trap is open');
ERROR: trap is open
DETAIL: Plunit.assertation fails (assert_false).
CONTEXT: referenced column: assert_false
select plunit.assert_false(NULL);
ERROR: plunit.assert_false exception
DETAIL: Plunit.assertation fails (assert_false).
CONTEXT: referenced column: assert_false
select plunit.assert_null(current_date);
ERROR: plunit.assert_null exception
DETAIL: Plunit.assertation fails (assert_null).
CONTEXT: referenced column: assert_null
select plunit.assert_null(NULL::date);
assert_null
-------------
(1 row)
select plunit.assert_not_null(current_date);
assert_not_null
-----------------
(1 row)
select plunit.assert_not_null(NULL::date);
ERROR: plunit.assert_not_null exception
DETAIL: Plunit.assertation fails (assert_not_null).
CONTEXT: referenced column: assert_not_null
select plunit.assert_equals('Pavel','Pa'||'vel');
assert_equals
---------------
(1 row)
select plunit.assert_equals(current_date, current_date + 1, 'diff dates');
ERROR: diff dates
DETAIL: Plunit.assertation fails (assert_equals).
CONTEXT: referenced column: assert_equals
select plunit.assert_equals(10.2, 10.3, 0.5);
assert_equals
---------------
(1 row)
select plunit.assert_equals(10.2, 10.3, 0.01, 'attention some diff');
ERROR: attention some diff
DETAIL: Plunit.assertation fails (assert_equals).
CONTEXT: referenced column: assert_equals
select plunit.assert_not_equals(current_date, current_date + 1, 'yestarday is today');
assert_not_equals
-------------------
(1 row)
select plunit.fail();
ERROR: plunit.assert_fail exception
DETAIL: Plunit.assertation (assert_fail).
CONTEXT: referenced column: fail
select plunit.fail('custom exception');
ERROR: custom exception
DETAIL: Plunit.assertation (assert_fail).
CONTEXT: referenced column: fail
SELECT dump('Yellow dog'::text) ~ E'^Typ=25 Len=(\\d+): \\d+(,\\d+)*$' AS t;
t
---
t
(1 row)
SELECT dump('Yellow dog'::text, 10) ~ E'^Typ=25 Len=(\\d+): \\d+(,\\d+)*$' AS t;
t
---
t
(1 row)
SELECT dump('Yellow dog'::text, 17) ~ E'^Typ=25 Len=(\\d+): .(,.)*$' AS t;
t
---
t
(1 row)
SELECT dump(10::int2) ~ E'^Typ=25 Len=6: \\d+(,\\d+){5}$' AS t;
t
---
t
(1 row)
SELECT dump(10::int4) ~ E'^Typ=25 Len=6: \\d+(,\\d+){5}$' AS t;
t
---
t
(1 row)
SELECT dump(10::int8) ~ E'^Typ=25 Len=6: \\d+(,\\d+){5}$' AS t;
t
---
t
(1 row)
SELECT dump(10.23::float4) ~ E'^Typ=25 Len=9: \\d+(,\\d+){8}$' AS t;
t
---
t
(1 row)
SELECT dump(10.23::float8) ~ E'^Typ=25 Len=9: \\d+(,\\d+){8}$' AS t;
t
---
t
(1 row)
SELECT dump(10.23::numeric) ~ E'^Typ=25 Len=(\\d+): \\d+(,\\d+)*$' AS t;
t
---
t
(1 row)
SELECT dump('2008-10-10'::date) ~ E'^Typ=25 Len=23: \\d+(,\\d+){22}$' AS t;
t
---
t
(1 row)
SELECT dump('2008-10-10'::timestamp) ~ E'^Typ=25 Len=23: \\d+(,\\d+){22}$' AS t;
t
---
t
(1 row)
SELECT dump('2009-10-10'::timestamp) ~ E'^Typ=25 Len=23: \\d+(,\\d+){22}$' AS t;
t
---
t
(1 row)
-- Tests for to_multi_byte
SELECT to_multi_byte('123$test');
to_multi_byte
------------------
１２３＄ｔｅｓｔ
(1 row)
-- Check internal representation difference
SELECT octet_length('abc');
octet_length
--------------
3
(1 row)
SELECT octet_length(to_multi_byte('abc'));
octet_length
--------------
9
(1 row)
-- Tests for to_single_byte
SELECT to_single_byte('123$test');
to_single_byte
----------------
123$test
(1 row)
SELECT to_single_byte('１２３＄ｔｅｓｔ');
to_single_byte
----------------
123$test
(1 row)
-- Check internal representation difference
SELECT octet_length('ａｂｃ');
octet_length
--------------
9
(1 row)
SELECT octet_length(to_single_byte('ａｂｃ'));
octet_length
--------------
3
(1 row)
-- Tests for round(TIMESTAMP WITH TIME ZONE)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','YEAR') = '1991-01-01 00:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'05/08/1990 05:35:25','Q') = '1990-04-01 00:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','MONTH') = '1990-12-01 00:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','DDD') = '1990-12-08 00:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','DAY') = '1990-12-09 00:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','hh') = '1990-12-08 06:00:00';
?column?
----------
t
(1 row)
select round(TIMESTAMP WITH TIME ZONE'12/08/1990 05:35:25','mi') = '1990-12-08 05:35:00';
?column?
----------
t
(1 row)
SET search_path TO default;
SET
--
-- Note: each Japanese character used below has display width of 2, otherwise 1.
-- Note: each output string is surrounded by '|' for improved readability
--
--
-- test LPAD family of functions
--
/* cases where one or more arguments are of type CHAR */
SELECT '|' || oracle.lpad('あbcd'::char(8), 10) || '|';
?column?
--------------
| あbcd |
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(8), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(8), 1) || '|';
?column?
----------
| |
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|x x あbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|xあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(5), 10, 'xい'::text) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::char(5), 10, 'xい'::nvarchar2(3)) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 10, 'xい'::char(3)) || '|';
?column?
--------------
|x x あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|x x あbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|xあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|x x あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcd|
(1 row)
/* test oracle.lpad(text, int [, text]) */
SELECT '|' || oracle.lpad('あbcd'::text, 10) || '|';
?column?
--------------
| あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(10), 10) || '|';
?column?
--------------
| あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(10), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(10), 10) || '|';
?column?
--------------
| あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(10), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 10, 'xい'::text) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::text, 10, 'xい'::nvarchar2(3)) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 10, 'xい'::text) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::varchar2(5), 10, 'xい'::nvarchar2(5)) || '|';
?column?
--------------
|xいxいあbc|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(5), 10, 'xい'::text) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
SELECT '|' || oracle.lpad('あbcd'::nvarchar2(5), 10, 'xい'::nvarchar2(5)) || '|';
?column?
--------------
| xいxあbcd|
(1 row)
--
-- test RPAD family of functions
--
/* cases where one or more arguments are of type CHAR */
SELECT '|' || oracle.rpad('あbcd'::char(8), 10) || '|';
?column?
--------------
|あbcd |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(8), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(8), 1) || '|';
?column?
----------
| |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|あbcx x |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcx|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(5), 10, 'xい'::text) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::char(5), 10, 'xい'::nvarchar2(3)) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 10, 'xい'::char(3)) || '|';
?column?
--------------
|あbcdx x |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|あbcx x |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcx|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(5), 10, 'xい'::char(3)) || '|';
?column?
--------------
|あbcdx x |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(5), 5, 'xい'::char(3)) || '|';
?column?
----------
|あbcd|
(1 row)
/* test oracle.lpad(text, int [, text]) */
SELECT '|' || oracle.rpad('あbcd'::text, 10) || '|';
?column?
--------------
|あbcd |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(10), 10) || '|';
?column?
--------------
|あbcd |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(10), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(10), 10) || '|';
?column?
--------------
|あbcd |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(10), 5) || '|';
?column?
----------
|あbcd|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 10, 'xい'::text) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::text, 10, 'xい'::nvarchar2(3)) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(5), 10, 'xい'::text) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::varchar2(5), 10, 'xい'::nvarchar2(5)) || '|';
?column?
--------------
|あbcxいxい|
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(5), 10, 'xい'::text) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(5), 10, 'xい'::varchar2(5)) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
SELECT '|' || oracle.rpad('あbcd'::nvarchar2(5), 10, 'xい'::nvarchar2(5)) || '|';
?column?
--------------
|あbcdxいx |
(1 row)
--
-- test TRIM family of functions
--
/* test that trailing blanks of CHAR arguments are not removed and are significant */
--
-- LTRIM
--
SELECT '|' || oracle.ltrim(' abcd'::char(10)) || '|' as LTRIM;
ltrim
-------------
|abcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd'::char(10),'a'::char(3)) || '|' as LTRIM;
ltrim
------------
|bcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd'::char(10),'a'::text) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd'::char(10),'a'::varchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd'::char(10),'a'::nvarchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd '::text,'a'::char(3)) || '|' as LTRIM;
ltrim
---------
|bcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd '::varchar2(10),'a'::char(3)) || '|' as LTRIM;
ltrim
---------
|bcd |
(1 row)
SELECT '|' || oracle.ltrim(' abcd '::nvarchar2(10),'a'::char(3)) || '|' as LTRIM;
ltrim
---------
|bcd |
(1 row)
--
-- RTRIM
--
SELECT '|' || oracle.rtrim(' abcd'::char(10)) || '|' as LTRIM;
ltrim
---------
| abcd|
(1 row)
SELECT '|' || oracle.rtrim(' abcd'::char(10),'d'::char(3)) || '|' as LTRIM;
ltrim
--------
| abc|
(1 row)
SELECT '|' || oracle.rtrim(' abcd'::char(10),'d'::text) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.rtrim(' abcd'::char(10),'d'::varchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.rtrim(' abcd'::char(10),'d'::nvarchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.rtrim(' abcd '::text,'d'::char(3)) || '|' as LTRIM;
ltrim
--------
| abc|
(1 row)
SELECT '|' || oracle.rtrim(' abcd '::varchar2(10),'d'::char(3)) || '|' as LTRIM;
ltrim
--------
| abc|
(1 row)
SELECT '|' || oracle.rtrim(' abcd '::nvarchar2(10),'d'::char(3)) || '|' as LTRIM;
ltrim
--------
| abc|
(1 row)
--
-- BTRIM
--
SELECT '|' || oracle.btrim(' abcd'::char(10)) || '|' as LTRIM;
ltrim
--------
|abcd|
(1 row)
SELECT '|' || oracle.btrim(' abcd'::char(10),'ad'::char(3)) || '|' as LTRIM;
ltrim
-------
|bc|
(1 row)
SELECT '|' || oracle.btrim(' abcd'::char(10),'ad'::text) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.btrim(' abcd'::char(10),'ad'::varchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.btrim(' abcd'::char(10),'ad'::nvarchar2(3)) || '|' as LTRIM;
ltrim
--------------
| abcd |
(1 row)
SELECT '|' || oracle.btrim(' abcd '::text,'d'::char(3)) || '|' as LTRIM;
ltrim
-------
|abc|
(1 row)
SELECT '|' || oracle.btrim(' abcd '::varchar2(10),'d'::char(3)) || '|' as LTRIM;
ltrim
-------
|abc|
(1 row)
SELECT '|' || oracle.btrim(' abcd '::nvarchar2(10),'d'::char(3)) || '|' as LTRIM;
ltrim
-------
|abc|
(1 row)
--
-- test oracle.length()
--
/* test that trailing blanks are not ignored */
SELECT oracle.length('あbb'::char(6));
length
--------
4
(1 row)
SELECT oracle.length(''::char(6));
length
--------
(1 row)
--
-- test plvdate.bizdays_between
--
SELECT plvdate.including_start();
including_start
-----------------
t
(1 row)
SELECT plvdate.bizdays_between('2016-02-24','2016-02-26');
bizdays_between
-----------------
3
(1 row)
SELECT plvdate.bizdays_between('2016-02-21','2016-02-27');
bizdays_between
-----------------
5
(1 row)
SELECT plvdate.include_start(false);
include_start
---------------
(1 row)
SELECT plvdate.bizdays_between('2016-02-24','2016-02-26');
bizdays_between
-----------------
2
(1 row)
SELECT plvdate.bizdays_between('2016-02-21','2016-02-27');
bizdays_between
-----------------
5
(1 row)
SELECT oracle.round(1.234::double precision, 2), oracle.trunc(1.234::double precision, 2);
round | trunc
-------+-------
1.23 | 1.23
(1 row)
SELECT oracle.round(1.234::float, 2), oracle.trunc(1.234::float, 2);
round | trunc
-------+-------
1.23 | 1.23
(1 row)
--
-- should not fail - fix: Crashes due to insufficent argument checking (#59)
--
select dbms_random.string(null, 42);
ERROR: an argument is NULL
CONTEXT: referenced column: string
select dbms_pipe.create_pipe(null);
ERROR: pipe name is NULL
DETAIL: Pipename may not be NULL.
CONTEXT: referenced column: create_pipe
select plunit.assert_not_equals(1,2,3);
ERROR: plunit.assert_not_equal exception
DETAIL: Plunit.assertation fails (assert_not_equals).
CONTEXT: referenced column: assert_not_equals
--
-- lexer text
--
SELECT pos, token, class, mod FROM plvlex.tokens('select * from a.b.c join d on x=y', true, true);
pos | token | class | mod
-----+--------+---------+------
0 | select | KEYWORD |
7 | * | OTHERS | self
9 | from | KEYWORD |
14 | a.b.c | IDENT |
20 | join | KEYWORD |
25 | d | IDENT |
27 | on | KEYWORD |
30 | x | IDENT |
31 | = | OTHERS | self
32 | y | IDENT |
(10 rows)
--
-- trigger functions
--
CREATE TABLE trg_test(a varchar, b int, c varchar, d date, e int);
CREATE TABLE
CREATE TRIGGER trg_test_xx BEFORE INSERT OR UPDATE
ON trg_test FOR EACH ROW EXECUTE PROCEDURE oracle.replace_empty_strings(true);
WARNING: Trigger function with non-plpgsql type is not recommended.
DETAIL: Non-plpgsql trigger function are not shippable by default.
HINT: Unshippable trigger may lead to bad performance.
CREATE TRIGGER
\pset null ***
Null display is "***".
INSERT INTO trg_test VALUES('',10, 'AHOJ', NULL, NULL);
INSERT 0 1
INSERT INTO trg_test VALUES('AHOJ', NULL, '', '2020-01-01', 100);
INSERT 0 1
SELECT * FROM trg_test;
a | b | c | d | e
------+-----+------+---------------------+-----
*** | 10 | AHOJ | *** | ***
AHOJ | *** | *** | 2020-01-01 00:00:00 | 100
(2 rows)
DELETE FROM trg_test;
DELETE 2
DROP TRIGGER trg_test_xx ON trg_test;
DROP TRIGGER
CREATE TRIGGER trg_test_xx BEFORE INSERT OR UPDATE
ON trg_test FOR EACH ROW EXECUTE PROCEDURE oracle.replace_null_strings();
WARNING: Trigger function with non-plpgsql type is not recommended.
DETAIL: Non-plpgsql trigger function are not shippable by default.
HINT: Unshippable trigger may lead to bad performance.
CREATE TRIGGER
INSERT INTO trg_test VALUES(NULL, 10, 'AHOJ', NULL, NULL);
INSERT 0 1
INSERT INTO trg_test VALUES('AHOJ', NULL, NULL, '2020-01-01', 100);
INSERT 0 1
SELECT * FROM trg_test;
a | b | c | d | e
------+-----+------+---------------------+-----
| 10 | AHOJ | *** | ***
AHOJ | *** | | 2020-01-01 00:00:00 | 100
(2 rows)
DROP TABLE trg_test;
DROP TABLE
SELECT oracle.unistr('\0441\043B\043E\043D');
unistr
--------
слон
(1 row)
SELECT oracle.unistr('d\u0061t\U00000061');
unistr
--------
data
(1 row)
-- run-time error
SELECT oracle.unistr('wrong: \db99');
ERROR: invalid Unicode surrogate pair
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \db99\0061');
ERROR: invalid Unicode surrogate pair
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \+00db99\+000061');
ERROR: invalid Unicode surrogate pair
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \+2FFFFF');
ERROR: invalid Unicode escape value
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \udb99\u0061');
ERROR: invalid Unicode surrogate pair
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \U0000db99\U00000061');
ERROR: invalid Unicode surrogate pair
CONTEXT: referenced column: unistr
SELECT oracle.unistr('wrong: \U002FFFFF');
ERROR: invalid Unicode escape value
CONTEXT: referenced column: unistr
select plvstr.is_prefix(44556677, -44);
is_prefix
-----------
f
(1 row)
select plvstr.is_prefix(-44556677, -44);
is_prefix
-----------
t
(1 row)
select plvstr.is_prefix(44556677, 44);
is_prefix
-----------
t
(1 row)
select plvstr.is_prefix(-44556677, 44);
is_prefix
-----------
f
(1 row)
