| \set ECHO none |
| -- |
| -- test built-in date type oracle compatibility functions |
| -- |
| SELECT add_months ('2003-08-01', 3); |
| add_months |
| ------------ |
| 2003-11-01 |
| (1 row) |
| |
| SELECT add_months ('2003-08-01', -3); |
| add_months |
| ------------ |
| 2003-05-01 |
| (1 row) |
| |
| SELECT add_months ('2003-08-21', -3); |
| add_months |
| ------------ |
| 2003-05-21 |
| (1 row) |
| |
| SELECT add_months ('2003-01-31', 1); |
| add_months |
| ------------ |
| 2003-02-28 |
| (1 row) |
| |
| SELECT add_months ('2008-02-28', 1); |
| add_months |
| ------------ |
| 2008-03-28 |
| (1 row) |
| |
| SELECT add_months ('2008-02-29', 1); |
| add_months |
| ------------ |
| 2008-03-31 |
| (1 row) |
| |
| SELECT add_months ('2008-01-31', 12); |
| add_months |
| ------------ |
| 2009-01-31 |
| (1 row) |
| |
| SELECT add_months ('2008-01-31', -12); |
| add_months |
| ------------ |
| 2007-01-31 |
| (1 row) |
| |
| SELECT add_months ('2008-01-31', 95903); |
| add_months |
| ------------ |
| 9999-12-31 |
| (1 row) |
| |
| SELECT add_months ('2008-01-31', -80640); |
| add_months |
| --------------- |
| 4712-01-31 BC |
| (1 row) |
| |
| SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd')); |
| last_day |
| ------------ |
| 2003-03-31 |
| (1 row) |
| |
| SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd')); |
| last_day |
| ------------ |
| 2003-02-28 |
| (1 row) |
| |
| SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd')); |
| last_day |
| ------------ |
| 2004-02-29 |
| (1 row) |
| |
| SELECT last_day('1900-02-01'); |
| last_day |
| ------------ |
| 1900-02-28 |
| (1 row) |
| |
| SELECT last_day('2000-02-01'); |
| last_day |
| ------------ |
| 2000-02-29 |
| (1 row) |
| |
| SELECT last_day('2007-02-01'); |
| last_day |
| ------------ |
| 2007-02-28 |
| (1 row) |
| |
| SELECT last_day('2008-02-01'); |
| last_day |
| ------------ |
| 2008-02-29 |
| (1 row) |
| |
| SELECT next_day ('2003-08-01', 'TUESDAY'); |
| next_day |
| ------------ |
| 2003-08-05 |
| (1 row) |
| |
| SELECT next_day ('2003-08-06', 'WEDNESDAY'); |
| next_day |
| ------------ |
| 2003-08-13 |
| (1 row) |
| |
| SELECT next_day ('2003-08-06', 'SUNDAY'); |
| next_day |
| ------------ |
| 2003-08-10 |
| (1 row) |
| |
| SELECT next_day ('2008-01-01', 'sun'); |
| next_day |
| ------------ |
| 2008-01-06 |
| (1 row) |
| |
| SELECT next_day ('2008-01-01', 'sunAAA'); |
| next_day |
| ------------ |
| 2008-01-06 |
| (1 row) |
| |
| SELECT next_day ('2008-01-01', 1); |
| next_day |
| ------------ |
| 2008-01-06 |
| (1 row) |
| |
| SELECT next_day ('2008-01-01', 7); |
| next_day |
| ------------ |
| 2008-01-05 |
| (1 row) |
| |
| SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd')); |
| months_between |
| ------------------- |
| -2.41935483870968 |
| (1 row) |
| |
| SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd')); |
| months_between |
| ------------------ |
| 3.58064516129032 |
| (1 row) |
| |
| SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd')); |
| months_between |
| ---------------- |
| 0 |
| (1 row) |
| |
| SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd')); |
| months_between |
| ---------------- |
| 2 |
| (1 row) |
| |
| 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) |
| |
| 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 |
| ---------------- |
| 12 |
| (1 row) |
| |
| select dbms_pipe.unpack_message_date(); |
| unpack_message_date |
| --------------------- |
| 2006-10-11 |
| (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 name, items, "limit", private, owner from dbms_pipe.db_pipes where name = 'bob'; |
| name | items | limit | private | owner |
| ------+-------+-------+---------+------- |
| bob | 1 | 10 | f | |
| (1 row) |
| |
| 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.', ''); |
| ERROR: too few parameters specified for template string |
| select plvsubst.string('My name is empty.', ''); |
| string |
| ------------------- |
| My name is empty. |
| (1 row) |
| |
| select round(to_date ('22-AUG-03', 'DD-MON-YY'),'YEAR') = to_date ('01-JAN-04', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select round(to_date ('22-AUG-03', 'DD-MON-YY'),'Q') = to_date ('01-OCT-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select round(to_date ('22-AUG-03', 'DD-MON-YY'),'MONTH') = to_date ('01-SEP-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select round(to_date ('22-AUG-03', 'DD-MON-YY'),'DDD') = to_date ('22-AUG-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select round(to_date ('22-AUG-03', 'DD-MON-YY'),'DAY') = to_date ('24-AUG-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'YEAR') = to_date ('01-JAN-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'Q') = to_date ('01-JUL-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'MONTH') = to_date ('01-AUG-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'DDD') = to_date ('22-AUG-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'DAY') = to_date ('17-AUG-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select next_day(to_date('01-Aug-03', 'DD-MON-YY'), 'TUESDAY') = to_date ('05-Aug-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select next_day(to_date('06-Aug-03', 'DD-MON-YY'), 'WEDNESDAY') = to_date ('13-Aug-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| select next_day(to_date('06-Aug-03', 'DD-MON-YY'), 'SUNDAY') = to_date ('10-Aug-03', 'DD-MON-YY'); |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| 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? |
| ---------- |
| t |
| (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 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 decode(1, 1, 100, 2, 200); |
| decode |
| -------- |
| 100 |
| (1 row) |
| |
| select decode(2, 1, 100, 2, 200); |
| decode |
| -------- |
| 200 |
| (1 row) |
| |
| select decode(3, 1, 100, 2, 200); |
| decode |
| -------- |
| |
| (1 row) |
| |
| select decode(3, 1, 100, 2, 200, 300); |
| decode |
| -------- |
| 300 |
| (1 row) |
| |
| select decode(NULL, 1, 100, NULL, 200, 300); |
| decode |
| -------- |
| 200 |
| (1 row) |
| |
| select decode('1'::text, '1', 100, '2', 200); |
| decode |
| -------- |
| 100 |
| (1 row) |
| |
| select decode(2, 1, 'ABC', 2, 'DEF'); |
| decode |
| -------- |
| DEF |
| (1 row) |
| |
| select decode('2009-02-05'::date, '2009-02-05', 'ok'); |
| decode |
| -------- |
| ok |
| (1 row) |
| |
| select decode('2009-02-05 01:02:03'::timestamp, '2009-02-05 01:02:03', 'ok'); |
| decode |
| -------- |
| ok |
| (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.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'; |
| select to_char(22); |
| to_char |
| --------- |
| 22 |
| (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.456001 |
| (1 row) |
| |
| select to_char(1234.5678::double precision); |
| to_char |
| ------------- |
| 1234.567800 |
| (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_number('123'::text); |
| to_number |
| ----------- |
| 123 |
| (1 row) |
| |
| SELECT to_number('123.456'::text); |
| to_number |
| ----------- |
| 123.456 |
| (1 row) |
| |
| SELECT to_date('2009-01-02'); |
| to_date |
| ------------ |
| 2009-01-02 |
| (1 row) |
| |
| SELECT bitand(5,1), bitand(5,2), bitand(5,4); |
| bitand | bitand | bitand |
| --------+--------+-------- |
| 1 | 0 | 4 |
| (1 row) |
| |
| SELECT sinh(1.570796), cosh(1.570796), tanh(4); |
| sinh | cosh | tanh |
| -----------------+------------------+------------------- |
| 2.3012980823207 | 2.50917772660545 | 0.999329299739067 |
| (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 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 |
| select dbms_assert.schema_name('dbms_assert'); |
| schema_name |
| ------------- |
| dbms_assert |
| (1 row) |
| |
| select dbms_assert.schema_name('jabadabado'); |
| ERROR: invalid 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 |
| 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 |
| select plunit.assert_true(NULL); |
| ERROR: plunit.assert_true exception |
| DETAIL: Plunit.assertation fails (assert_true). |
| select plunit.assert_true(1 = 2); |
| ERROR: plunit.assert_true exception |
| DETAIL: Plunit.assertation fails (assert_true). |
| select plunit.assert_true(1 = 2, 'one is not two'); |
| ERROR: one is not two |
| DETAIL: Plunit.assertation fails (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). |
| select plunit.assert_false(1 = 1, 'trap is open'); |
| ERROR: trap is open |
| DETAIL: Plunit.assertation fails (assert_false). |
| select plunit.assert_false(NULL); |
| ERROR: plunit.assert_false exception |
| DETAIL: Plunit.assertation fails (assert_false). |
| select plunit.assert_null(current_date); |
| ERROR: plunit.assert_null exception |
| DETAIL: Plunit.assertation fails (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). |
| 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). |
| 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). |
| 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). |
| select plunit.fail('custom exception'); |
| ERROR: custom exception |
| DETAIL: Plunit.assertation (assert_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=21 Len=2: \\d+(,\\d+){1}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump(10::int4) ~ E'^Typ=23 Len=4: \\d+(,\\d+){3}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump(10::int8) ~ E'^Typ=20 Len=8: \\d+(,\\d+){7}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump(10.23::float4) ~ E'^Typ=700 Len=4: \\d+(,\\d+){3}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump(10.23::float8) ~ E'^Typ=701 Len=8: \\d+(,\\d+){7}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump(10.23::numeric) ~ E'^Typ=1700 Len=(\\d+): \\d+(,\\d+)*$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump('2008-10-10'::date) ~ E'^Typ=1082 Len=4: \\d+(,\\d+){3}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| SELECT dump('2008-10-10'::timestamp) ~ E'^Typ=1114 Len=8: \\d+(,\\d+){7}$' AS t; |
| t |
| --- |
| t |
| (1 row) |
| |
| select listagg(i::text) from generate_series(1,3) g(i); |
| listagg |
| --------- |
| 123 |
| (1 row) |
| |
| select listagg(i::text, ',') from generate_series(1,3) g(i); |
| listagg |
| --------- |
| 1,2,3 |
| (1 row) |
| |
| select coalesce(listagg(i::text), '<NULL>') from (SELECT ''::text) g(i); |
| coalesce |
| ---------- |
| |
| (1 row) |
| |
| select coalesce(listagg(i::text), '<NULL>') from generate_series(1,0) g(i); |
| coalesce |
| ---------- |
| <NULL> |
| (1 row) |
| |
