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.