| Oracle functions and Oracle packages |
| ==================================== |
| This module contains some useful function which can help with porting |
| Oracle application to PostgreSQL or can be useful generally. |
| |
| Built-in Oracle date functions have been tested against Oracle 10 for |
| conformance. Date ranges from 1960 to 2070 work correctly. Dates before |
| 1100-03-01 cannot be verified due to a bug in Oracle. |
| |
| All functions are fully compatible with Oracle and respect all known |
| format strings. You can find more detailed descriptions on the |
| Internet using keywords "oracle round trunc date iyyy". |
| |
| List of format strings for trunc, round functions |
| ------------------------------------------------- |
| Y,YY,YYY,YYYY,SYYY,SYEAR year |
| I,IY,IYY,IYYY iso year |
| Q, quarter |
| WW week, day as first day of year |
| IW week, beginning Monday |
| W week, day as first day of month |
| DAY,DY,D first day of week, sunday |
| MONTH,MON,MM,RM month |
| CC,SCC century |
| DDD,DD,J day |
| HH,HH12,HH24 hour |
| MI minute |
| |
| Dates are always rounded upwards. That is, a date of July 1st will be |
| rounded to the next year. The 16th of July will be rounded to August. |
| |
| Date Functions |
| ============== |
| |
| o add_months(date, integer) date - Returns date plus n months |
| |
| add_months(date '2005-05-31',1) -> 2005-06-30 |
| |
| |
| o last_date(date) date - Returns last day of the month based on a date value |
| |
| last_day(date '2005-05-24') -> 2005-05-31 |
| |
| |
| o next_day(date, text) date - Returns the first weekday that is greater |
| than a date value |
| |
| next_day(date '2005-05-24', 'monday') -> 2005-05-30 |
| |
| o next_day(date, integer) date - Same as above. The second argument should |
| be 1..7 and interpreted as Sunday..Saturday. |
| |
| next_day(date '2005-05-24', 1) -> 2005-05-30 |
| |
| o months_between(date, date) float8 - Returns the number of months between |
| date1 and date2. If a fractional month is calculated, the months_between |
| function calculates the fraction based on a 31-day month. |
| |
| months_between(date '1995-02-02', date '1995-01-01') -> 1.0322580645161 |
| |
| o trunc(date, text) date - truncate date according to the specified format |
| |
| trunc(date '2005-07-12', 'iw') -> 2005-07-11 |
| |
| o round(date, text) date - will round dates according to the specified format |
| |
| round(date '2005-07-12', 'yyyy') -> 2006-01-01 |
| |
| |
| Table dual |
| ========== |
| PostgreSQL does not need Oracle's table 'dual', but since it is |
| expected by Oracle users, we create it. |
| |
| Package dbms_output |
| =================== |
| |
| $$$ REVIEW |
| |
| PostgreSQL sends information to the client via RAISE NOTICE, while |
| Oracle uses dbms_output.put_line(), which works differently. Oracle |
| has a queue per session. The function put_line() adds a line to the |
| queue and the function get_line() reads from the queue. If the flag |
| 'serveroutput' is set, then client over all sql statements reads |
| queue. You can use: |
| |
| select dbms_output.enable(); |
| select dbms_output.put_line('first_line'); |
| select dbms_output.put_line('next_line'); |
| select * from dbms_output.get_lines(0); |
| |
| or |
| select dbms_output.enable(); |
| select dbms_output.serveroutput('t'); |
| select dbms_output.put_line('first_line'); |
| |
| This package contains the following functions: enable(), disable(), |
| serveroutput(), put(), put_line(), new_line(), get_line(), get_lines(). |
| The package queue is implemented in the session's local memory. |
| |
| Package utl_file |
| ================ |
| This package lets PL/pgSQL programs read from and write to any files |
| that are accessible from the server. Every session can open at most ten |
| files and the maximum line size is 32K. This package contains |
| the following functions: |
| |
| * utl_file.fclose(file utl_file.file_type) - close file |
| * utl_file.fclose_all() - close all files |
| * utl_file.fcopy(src_location, src_filename, dest_location, dest_filename[, start_line][, end_line]) - copy text file |
| * utl_file.fflush(file utl_file.file_type) - flushes all data from buffers |
| * utl_file.fgetattr(location, filename) - get file attributes |
| * utl_file.fopen(location text, filename text, file_mode text [, maxlinesize int] [, encoding name]) utl_file.file_type - open file |
| * utl_file.fremove(location, filename) - remove file |
| * utl_file.frename(location, filename, dest_dir, dest_file[, overwrite]) - rename file |
| * utl_file.get_line(file utl_file.file_type) text - read one line from file |
| * utl_file.get_nextline(file utl_file.file_type) text - read one line from file or returns NULL |
| * utl_file.is_open(file utl_file.file_type) bool - returns true, if file is opened |
| * utl_file.new_line(file utl_file.file_type [,rows int]) - puts some new line chars to file |
| * utl_file.put(file utl_file.file_type, buffer text) - puts buffer to file |
| * utl_file.put_line(file utl_file.file_type, buffer text) - puts line to file |
| * utl_file.putf(file utl_file.file_type, format buffer [,arg1 text][,arg2 text][..][,arg5 text]) - put formated text into file |
| * utl_file.tmpdir() - get path of temp directory |
| |
| Because PostgreSQL doesn't support call by reference, some functions |
| are slightly different: fclose and get_line. |
| |
| declare f utl_file.file_type; |
| begin |
| f := utl_file.fopen('/tmp', 'sample.txt', 'r'); |
| <<readl>> |
| loop |
| begin |
| raise notice '%', utl_file.get_line(f); |
| exception |
| when no_data_found then |
| exit readl; |
| end; |
| end loop; |
| f := fclose(f); |
| end; |
| |
| or second (with PostgreSQL specific function get_nextline) |
| |
| declare |
| f utl_file.file_type; |
| line text; |
| begin |
| f := utl_file.fopen('/tmp', 'sample.txt', 'r'); |
| loop |
| line := utl_file.get_nextline(f); |
| exit when line is NULL; |
| raise notice '%', line; |
| exception |
| when others then |
| utl_file.fclose_all(); |
| end; |
| |
| Before using this package you have to set table |
| utl_file.utl_file_dir. This contains all allowed directories without |
| ending symbol ('/' or '\'). On WinNT platform you have to specify the |
| locality parameter with a final symbol '\' every time. |
| |
| Package dbms_pipe |
| ================= |
| This package is an emulation of Oracle's package dbms_pipe. It provides |
| inter-session communication. You can send and read any message with or without |
| waiting; list active pipes; set a pipe as private or public; and, use |
| explicit or implicit pipes. |
| |
| The maximum number of pipes is 50. |
| |
| Shared memory is used to send messages. |
| |
| An example follows: |
| |
| -- Session A |
| select dbms_pipe.create_pipe('my_pipe',10,true); -- explicit pipe creating |
| select dbms_pipe.pack_message('neco je jinak'); |
| select dbms_pipe.pack_message('anything is else'); |
| select dbms_pipe.send_message('my_pipe',20,0); -- change limit and send without waiting |
| select * from dbms_pipe.db_pipes; -- list of current pipes |
| |
| -- Session B |
| select dbms_pipe.receive_message('my_pipe',1); -- wait max 1 sec for message |
| select dbms_pipe.next_item_type(); -- -> 11, text |
| select dbms_pipe.unpack_message_text(); |
| select dbms_pipe.next_item_type(); -- -> 11, text |
| select dbms_pipe.unpack_message_text(); |
| select dbms_pipe.next_item_type(); -- -> 0, no more items |
| select dbms_pipe.remove_pipe('my_pipe'); |
| |
| There are some differences compared to Oracle, however: |
| o limit for pipes isn't in bytes but in elements in pipe |
| o you can send message without waiting |
| o you can send empty messages |
| o next_item_type knows about TIMESTAMP (type 13) |
| o PostgreSQL don't know about the RAW type, use bytea instead |
| |
| Package dbms_alert |
| ================== |
| Another means of inter-process communication. |
| |
| -- Session A |
| select dbms_alert.register('boo'); |
| select * from dbms_alert.waitany(10); |
| |
| -- Session B |
| select dbms_alert.register('boo'); |
| select * from dbms_alert.waitany(10); |
| |
| -- Session C |
| select dbms_alert.signal('boo','Nice day'); |
| |
| |
| Package PLVdate |
| =============== |
| This module contains functions for working with business days from |
| package PLVdate. Detailed documentation can be found in the PLVision |
| library. This package is multicultural, but default configurations |
| are only for European countries (see source code). |
| |
| You should define your own non-business days (max 50 days) and own |
| holidays (max 30 days). A holiday is any non-business day which is the |
| same every year. For example, Christmas day in Western countries. |
| |
| Functions: |
| ---------- |
| |
| o plvdate.add_bizdays(day date, days int) date - Get the date created by adding <n> business days to a date |
| |
| o plvdate.nearest_bizday(day date) date - Get the nearest business date to a given date, user defined |
| |
| o plvdate.next_bizday(day date) date - Get the next business date from a given date, user defined |
| |
| o plvdate.bizdays_between(day1 date, day2 date) int - Get the number of business days between two dates |
| |
| o plvdate.prev_bizday(day date) date - Get the previous business date from a given date |
| |
| o plvdate_isbizday(date) bool - Call this function to determine if a date is a business day |
| |
| o plvdate.set_nonbizday(dow varchar) - Set day of week as non-business day |
| |
| o plvdate.unset_nonbizday(dow varchar) - Unset day of week as non-business day |
| |
| o plvdate.set_nonbizday(day date) - Set day as non-business day |
| |
| o plvdate.unset_nonbizday(day date) - Unset day as non-business day |
| |
| o plvdate.set_nonbizday(day date, repeat bool) - Set day as non-business day, if 'repeat' is true, then day is nonbiz every year |
| |
| o plvdate.unset_nonbizday(day date, repeat bool) - Unset day as non-business day, if 'repeat' is true, then day is nonbiz every year |
| |
| o plvdate.use_easter() - Easter Sunday and Easter monday will be holiday |
| |
| o plvdate.unuse_easter(); |
| |
| o plvdate.use_easter(useit boolean); |
| |
| o plvdate.using_easter() bool - If we use Easter then returns true |
| |
| o plvdate.include_start() - Include starting date in bizdays_between calculation |
| |
| o plvdate.noinclude_start(); |
| |
| o plvdate.include_start(include boolean); |
| |
| o plvdate.including_start() bool; |
| |
| o plvdate.default_holidays(varchar) - load default configurations. You can currently use |
| the following configurations: |
| Czech, German*, Austria, Poland, Slovakia, Russia, GB* and USA. |
| |
| * configuration contains only common holidays for all regions. You can add |
| your own regional holiday with |
| plvdate.set_nonbizday(nonbizday, true) |
| |
| Example: |
| -------- |
| postgres=# select plvdate.default_holidays('czech'); |
| default_holidays |
| ------------------ |
| |
| (1 row) |
| postgres=# select to_char(current_date, 'day'), |
| plvdate.next_bizday(current_date), |
| to_char(plvdate.next_bizday(current_date),'day'); |
| to_char | next_bizday | to_char |
| -----------+-------------+----------- |
| saturday | 2006-03-13 | monday |
| (1 row) |
| |
| Change for non-European environment: |
| ------------------------------------ |
| select plvdate.unset_nonbizday('saturday'); |
| select plvdate.unset_nonbizday('sunday'); |
| select plvdate.set_nonbizday('friday'); |
| select plvdate.set_nonbizday('2006-05-19', true); |
| select plvdate.unuse_easter(); |
| |
| Package PLVstr and PLVchr |
| ========================= |
| This package contains some useful string and character functions. Each |
| function supports positive and negative offsets -- i.e., offset from the |
| end of the string. For example: |
| |
| plvstr.left('abcdef',2) -> ab |
| plvstr.left('abcdef',-2) -> abcd |
| plvstr.substr('abcdef',1,1) -> a |
| plvstr.substr('abcdef',-1,1) -> f |
| plvstr.substr('abcde',-2,1) -> d |
| |
| List of functions: |
| o plvstr.normalize(str text) - Normalize string - Replace multiple whitespace chars with single spaces |
| |
| o plvstr.is_prefix(str text, prefix text, cs bool) - Returns true, if prefix is prefix of str |
| |
| o plvstr.is_prefix(str text, prefix text) - Returns true, if prefix is prefix of str |
| |
| o plvstr.is_prefix(str int, prefix int) - Returns true, if prefix is prefix of str |
| |
| o plvstr.is_prefix(str bigint, prefix bigint) - Returns true, if prefix is prefix of str |
| |
| o plvstr.substr(str text, start int, len int) - Returns substring started on start_in to end |
| |
| o plvstr.substr(str text, start int) - Returns substring started on start_in to end |
| |
| o plvstr.instr(str text, patt text, start int, nth int) - Search pattern in string |
| |
| o plvstr.instr(str text, patt text, start int) - Search pattern in string |
| |
| o plvstr.instr(str text, patt text) - Search pattern in string |
| |
| o plvstr.lpart(str text, div text, start int, nth int, all_if_notfound bool) - Call this function to return the left part of a string |
| |
| o plvstr.lpart(str text, div text, start int, nth int) - Call this function to return the left part of a string |
| |
| o plvstr.lpart(str text, div text, start int) - Call this function to return the left part of a string |
| |
| o plvstr.lpart(str text, div text) - Call this function to return the left part of a string |
| |
| o plvstr.rpart(str text, div text, start int, nth int, all_if_notfound bool) - Call this function to return the right part of a string |
| |
| o plvstr.rpart(str text, div text, start int, nth int) - Call this function to return the right part of a string |
| |
| o plvstr.rpart(str text, div text, start int) - Call this function to return the right part of a string |
| |
| o plvstr.rpart(str text, div text) - Call this function to return the right part of a string |
| |
| o plvstr.lstrip(str text, substr text, num int) - Call this function to remove characters from the beginning |
| |
| o plvstr.lstrip(str text, substr text) - Call this function to remove characters from the beginning |
| |
| o plvstr.rstrip(str text, substr text, num int) - Call this function to remove characters from the end |
| |
| o plvstr.rstrip(str text, substr text) - Call this function to remove characters from the end |
| |
| o plvstr.rvrs(str text, start int, _end int) - Reverse string or part of string |
| |
| o plvstr.rvrs(str text, start int) - Reverse string or part of string |
| |
| o plvstr.rvrs(str text) - Reverse string or part of string |
| |
| o plvstr.left(str text, n int) - Returns first num_in characters. You can use negative num_in |
| |
| o plvstr.right(str text, n int) - Returns last num_in characters. You can use negative num_in |
| |
| o plvstr.swap(str text, replace text, start int, length int) - Replace a substring in a string with a specified string |
| |
| o plvstr.swap(str text, replace text) - Replace a substring in a string with a specified string |
| |
| o plvstr.betwn(str text, start int, _end int, inclusive bool) - Find the Substring Between Start and End Locations |
| |
| o plvstr.betwn(str text, start text, _end text, startnth int, endnth int, inclusive bool, gotoend bool) - Find the Substring Between Start and End Locations |
| |
| o plvstr.betwn(str text, start text, _end text) - Find the Substring Between Start and End Locations |
| |
| o plvstr.betwn(str text, start text, _end text, startnth int, endnth int) - Find the Substring Between Start and End Locations |
| |
| o plvchr.nth(str text, n int) - Call this function to return the Nth character in a string |
| |
| o plvchr.first(str text) - Call this function to return the first character in a string |
| |
| o plvchr.last(str text) - Call this function to return the last character in a string |
| |
| o plvchr.is_blank(c int) - Is blank |
| |
| o plvchr.is_blank(c text) - Is blank |
| |
| o plvchr.is_digit(c int) - Is digit |
| |
| o plvchr.is_digit(c text) - Is digit |
| |
| o plvchr.is_quote(c int) - Is quote |
| |
| o plvchr.is_quote(c text) - Is quote |
| |
| o plvchr.is_other(c int) - Is other |
| |
| o plvchr.is_other(c text) - Is other |
| |
| o plvchr.is_letter(c int) - Is letter |
| |
| o plvchr.is_letter(c text) - Is letter |
| |
| o plvchr.char_name(c text) - Returns the name of the character to ascii code as a VARCHAR. |
| |
| o plvchr.quoted1(str text) - Quoted text between ''' |
| |
| o plvchr.quoted2(str text) - Quoted text between '"' |
| |
| o plvchr.stripped(str text, char_in text) - Strips a string of all instances of the specified characters |
| |
| |
| Package PLVsubst |
| ================ |
| The PLVsubst package performs string substitutions based on a substitution keyword. |
| |
| o plvsubst.string(template_in text, vals_in text[]) - Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list |
| |
| o plvsubst.string(template_in text, vals_in text[], subst_in text) |
| |
| o plvsubst.string(template_in text, vals_in text, delim_in text) |
| |
| o plvsubst.string(template_in text, vals_in text, delim_in text, subst_in text) |
| |
| o plvsubst.setsubst(str text) - Set substitution keyword to default '%s' |
| |
| o plvsubst.subst() - Retrieve substitution keyword |
| |
| Examples: |
| --------- |
| |
| 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 %s %s.', 'Pavel,Stěhule'); |
| string |
| --------------------------- |
| My name is Pavel Stěhule. |
| (1 row) |
| |
| select plvsubst.string('My name is $$ $$.', 'Pavel|Stěhule','|','$$'); |
| string |
| --------------------------- |
| My name is Pavel Stěhule. |
| (1 row) |
| |
| |
| Package DBMS_utility |
| ==================== |
| o dms_utility.format_call_stack() -- return a formatted string with the contents |
| of the call stack |
| |
| postgres=# select foo2(); |
| foo2 |
| ---------------------------------- |
| ----- Call Stack ----- |
| line object |
| number statement name |
| 1 return function foo |
| 1 return function foo1 |
| 1 return function foo2 |
| (1 row) |
| |
| |
| Package PLVlex |
| ============== |
| This package isn't compatible with original PLVlex. |
| |
| postgres=# select * from |
| plvlex.tokens('select * from a.b.c join d ON x=y', true, true); |
| |
| pos | token | code | class | separator | mod |
| -----+--------+------+---------+-----------+------ |
| 0 | select | 527 | KEYWORD | | |
| 7 | * | 42 | OTHERS | | self |
| 9 | from | 377 | KEYWORD | | |
| 25 | a.b.c | | IDENT | | |
| 20 | join | 418 | KEYWORD | | |
| 25 | d | | IDENT | | |
| 27 | on | 473 | KEYWORD | | |
| 30 | x | | IDENT | | |
| 31 | = | 61 | OTHERS | | self |
| 32 | y | | IDENT | | |
| (10 rows) |
| |
| Warning: Keyword's codes can be changed between PostgreSQL versions! |
| o plvlex.tokens(str text, skip_spaces bool, qualified_names bool) - Returns table of lexical elements in str. |
| |
| DBMS_ASSERT |
| =========== |
| This package can protect user input against SQL injection. |
| |
| o dbms_assert.enquote_literal(varchar) varchar - Add leading and trailing quotes, |
| verify that all single quotes are paired with |
| adjacent single quotes. |
| |
| o dbms_assert.enquote_name(varchar [, boolean]) varchar - Enclose name in double quotes. Optional |
| second parameter ensure loweralize of name. Attention - |
| On Oracle is second parameter capitalize! |
| |
| o dbms_assert.noop(varchar) varchar - Returns value without any checking. |
| |
| o dbms_assert.qualified_sql_name(varchar) varchar - This function verifies that the input string |
| is qualified SQL name. |
| |
| o dbms_assert.schema_name(varchar) varchar - Function verifies that input string is an existing schema name. |
| |
| o dbms_assert.simple_sql_name(varchar) varchar -This function verifies that the input string is simple SQL name. |
| |
| o dbms_assert.object_name(varchar) varchar - Verifies that input string is qualified SQL identifier |
| of an existing SQL object. |
| |
| PLUnit |
| ====== |
| This unit contains some assert functions. |
| |
| o plunit.assert_true(bool [, varchar]) - Asserts that the condition is true. |
| |
| o plunit.assert_false(bool [, varchar]) - Asserts that the condition is false. |
| |
| o plunit.assert_null(anyelement [, varchar]) - Asserts that the actual is null. |
| |
| o plunit.assert_not_null(anyelement [, varchar]) - Asserts that the actual isn't null. |
| |
| o plunit.assert_equals(anyelement, anyelement [, double precision] [, varchar]) - |
| Asserts that expected and actual are equal. |
| |
| o plunit.assert_not_equals(anyelement, anyelement [, double precision] [, varchar]) - |
| Asserts that expected and actual are equal. |
| |
| o plunit.fail([varchar]) - Fail can be used to cause a test procedure to fail immediately using the supplied message. |
| |
| Package DBMS_random |
| =================== |
| o dbms_random.initialize(int) - Initialize package with a seed value. |
| |
| o dbms_random.normal() - Returns random numbers in a standard normal distribution. |
| |
| o dbms_random.random() - Returns random number from -2^31 .. 2^31. |
| |
| o dbms_random.seed(int) |
| o dbms_random.seed(text) - Reset seed value. |
| |
| o dbms_random.string(opt text(1), len int) - Create random string |
| |
| o dbms_random.terminate() - Terminate package (do nothing in Pg) |
| |
| o dbms_random.value() - Returns a random number from [0.0 - 1.0) |
| |
| o dbms_random.value(low double precision, high double precision) - Returns a random number from [low - high) |
| |
| Others functions |
| ================ |
| This module contains implementation of functions: concat, nvl, nvl2, lnnvl, decode, |
| bitand, nanvl, sinh, cosh, tanh and oracle.substr. |
| |
| o oracle.substr(str text, start int, len int) - Oracle compatible substring |
| o oracle.substr(str text, start int) - Oracle compatible substring |
| |
| o pg_catalog.listagg(str text [, separator text]) - aggregate values to list |
| o pg_catalog.median(float4) - calculate a median |
| o pg_catalog.median(float8) - calculate a median |
| |
| You might need to set search_path to 'oracle, pg_catalog, "$user", public' |
| because oracle.substr is installed side-by-side with pg_catalog.substr. |
| |
| ToDo: |
| o better documentation |
| o better serialization in dbms_pipe (via _send and _recv functions) |
| o alter shared memory structures by temporary tables |
| only locks are in shmem, (bitmaps), data in tmp tbl |
| |
| This module is released under the BSD license. |
| |
| Pavel Stehule |
| stehule@kix.fsv.cvut.cz |
| |
| Prague 2008 |
| |
| p.s. |
| If you use this library, please send me mail about it. |