Oracle functionality (en)

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

Translated by Kateřina Šmídová

For a moment developers seemed to copy Oracle to PostgreSQL. NON ANSI functions, that offer Oracle, were implemented to PostgreSQL. But this time is over. ANSI SQL not defined function has no chance to get to distribution in practice. The solution is to use EnterpriseDB shield, its purpose is Oracle compatibility, or to use supplementing function package Orafce. Beside a number of fundamental user requested functions contains a part of PLVision framework and dbms_alert, dbms_output and dbms_pipe packages. The aim is not full Oracle compatibility, this is technically not possible, but to expand PostgreSQL on advanced functions, which provides RDBMS Oracle. You can find lots of examples in Write your debugger PL/pgSQL or advanced PostgreSQL programming.

Google AdSense

Support

Please, visit google group.

Basic functions

Type date functions

Most of these functions has as one of parameters a period, that is given by code character in this table:

  SELECT add_months(date '2005-05-31',1);  -- > 2005-06-30
  SELECT last_day(date '2005-05-24');      -- > 2005-05-31
  SELECT next_day(date '2005-05-24', 'monday'); -- > 2005-05-30
  SELECT next_day(date '2005-05-24', 2); -- > 2005-05-30
  SELECT months_between(date '1995-02-02', date '1995-01-01'); -- > 1.0322580645161
  SELECT trunc(date '2005-07-12', 'iw');   -- > 2005-07-11
  SELECT round(date '2005-07-12', 'yyyy'); -- > 2006-01-01

Attention: This is PostgreSQL date type - in recent Orafce you can use a oracle.date type - that is more close to Oracle's date type.

Y,YY,YYY,YYYY,SYYY,SYEAR year
I,IY,IYY,IYYY iso year
Q, quarter
WW week, the first day in week is the first day in year
IW week, the first day is Monday
W week, the first day is the first day in month
DAY,DY,D week, the first day is Sunday
MONTH,MON,MM,RM month
CC,SCC century
DDD,DD,J day
HH,HH12,HH24 hour
MI minute

Function round round off 1st July for season, 16th day for month and Thursday for week.

add_months(date, integer) date Add n months
last_date(date) date Return the last day in month
next_day(date, text) date Return the first day in week after the given day, e.g. the first next Thursday
next_day(date, integer) date Same as above. The second argument should be 1..7 and interpreted as Sunday..Satday.
months_between(date, date) float8 Return the number of months between two dates. The calculation is based on month = 31 days.
trunc(date, text) date Cut date to engaged format
round(date, text) date Round date to engaged format

These functions were verificated by Oracle 10i.

Type oracle.date

The DATE data type in Oracle returns date and time values but PostgreSQL DATE data type returns only DATE so the behavior of Oracle DATE data type and PostgreSQL DATE data type is different. The oracle.date data type returns date and time which is same as Oracle DATE data type. If you want to use oracle compatible DATE type then use oracle.date type otherwise you can use pg_catalog.date which is available in PostgreSQL.

Example:

postgres=# SET search_path TO oracle,"$user", public, pg_catalog;
postgres=# CREATE TABLE oracle_date(col1 DATE);
postgres=# INSERT INTO oracle_date VALUES('2014-06-24 12:12:11'::DATE);
postgres=# SELECT * FROM oracle_date;
        col1         
---------------------
 2014-06-24 12:12:11
(1 row)

oracle.DATE functions

SELECT oracle.add_months(oracle.date'2005-05-31 10:12:12',1); -- > 2005-06-30 10:12:12
SELECT oracle.last_day(oracle.date '2005-05-24 11:12:12'); -- > 2005-05-31 11:12:12
SELECT oracle.next_day(oracle.date '2005-05-24 10:12:12', 'monday'); -- > 2005-05-30 10:12:12
SELECT oracle.next_day(oracle.date '2005-05-24 11:21:12', 1); -- > 2005-05-29 11:21:12
SELECT oracle.months_between(oracle.date '1995-02-02 10:00:00', oracle.date '1995-01-01 10:21:11'); -- > 1.03225806451613
SELECT oracle.to_date('02/16/09 04:12:12', 'MM/DD/YY HH24:MI:SS'); -- > 2009-02-16 04:12:12

You need to set search_path TO oracle,"$user", public, pg_catalog because the functions like oracle.add_months,oracle.last_day,oracle.next_day,oracle.months_between is installed side-by-side with pg_catalog.add_months,pg_catalog.last_day,pg_catalog.next_day,pg_catalog.months_between.

nlssort function

PostgreSQL now permits to sort by one elected rule. This limitation should be shifted in version 8.2 by so-called COLLATE. By then you can use nlssort function, that enables to use other rules (presumption is to install required national support (locales)). The function can be used with a parameter determining the sort rule or this parameter can be ready adjusted by set_nls_sort function. Nlssort function works correctly only if relevant encoding locales will be used. It is mistake to have latin2 encoding data and to sort them by German latin1 encoding locales. Contrary it is correct to use Czech and German sorting with UTF-8 encoding. For this function was used code by Jan Pazdziora from nls_string packet.


 SELECT * FROM peoples ORDER BY nlsstring(surname, 'de_DE.UTF-8');
 SELECT set_nls_sort('cs_CZ.UTF-8');
 SELECT * FROM peoples ORDER BY nlsstring(surname);

String functions

These functions have been taken in the packet only to shortcut porting aplications from Oracle.There are ANSI SQL equivalent to these functions generally.

instr (text, text, [int[, int]]) The instr function returns the location of a substring in a string.
reverse (text) Returns the reverse of the given string.
oracle.substr (text, [int [,int]]) The substr functions allows you to extract a substring from a string (PostgreSQL's substr function isn't compatible with Oracle).
listagg(text [, text]) The listagg function concate values to list.

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.

Other functions

These functions are in the package only to shortcut porting applications from Oracle. Use the more universal and ANSI standard COALESCE function instead.

nvl (anyelement, anyalement) Returns first non NULL parameter.
nvl2 (anyelement, anyememet, anyelement) Returns first non NULL parameter.
lnnvl (boolean) Return true if the argument is false or NULL, or false.
concat (text, text) The concat function allows you to concatenate two strings together. An difference between concat function and ANSI SQL operator || is tolerance to NULL value.
decode (lhs, [rhs1, result1], ... , default = NULL) Return resultN if lhs = rhsN, or default if no match.
bitand (bigint, bigint) Same as $1 & $2.
sinh (float8) Hyperbolic sine.
cosh (float8) Hyperbolic cosine.
tanh (float8) Hyperbolic tangent.
nanvl (float, float) Return $1 if not NaN, or $2 if NaN.
dump (anyexpr [, int]) Returns a text value that includes the datatype code,the length in bytes, and the internal representation of the expression.
listagg (str text [, separator text]) aggregate values to list
median (float4) calculate a median
median (float8) calculate a median
postgres=# select pg_catalog.dump(1);
         dump          
-----------------------
 Typ=23 Len=4: 1,0,0,0
(1 row)

postgres=# select pg_catalog.dump('Pavel Stehule',17);
                       dump                       
--------------------------------------------------
 Typ=25 Len=17: D,?,?,?,P,a,v,e,l, ,S,t,e,h,u,l,e
(1 row)

postgres=# select pg_catalog.dump('Pavel Stehule',10);
                                  dump                                   
-------------------------------------------------------------------------
 Typ=25 Len=17: 68,0,0,0,80,97,118,101,108,32,83,116,101,104,117,108,101
(1 row)

postgres=# select pg_catalog.dump(date '2007-10-10');
           dump            
---------------------------
 Typ=1082 Len=4: 23,11,0,0
(1 row)

DBMS_ALERT

This package supports asynchronous requisitioned notification. The functionality is very similar to PostgreSQL SQL notification that is realized by LISTEN/NOTIFY command. dbms_alert is solution, that is 100% realized on server, LISTEN/NOTIFY demand client application support (so you can not use it e.g. in PL saved procedures). User session has to require notification by care messages registration. This is the main difference to dbms_pipe, which is also intersession communication solution, but it is broadcast and only the first session assumes the message, which get to it. dbms_alert transmit the message to all register recipients. dbms_pipe is used for two-way communication between two user sessions and dbms_alert is used for one-way communication of one session to other.

  -- session pavel
  pavel# SELECT dbms_alert.register('ahoj');
  pavel# SELECT * FROM dbms_alert.waitany(NULL);
  
  -- session root
  root# SELECT dbms_alert.signal('ahoj','Gertrudo');

Functions

dbms_alert.register(name text) Register sending notification about name appearance
dbms_alert.remove(name text) Despatch registration of name reception
dbms_alert.removeall() Despatch all registrations
dbms_alert.signal(name text, message text) Signalling event
dbms_alert.waitany(OUT name text, OUT message text, OUT status integer, n float8) Wait max. n seconds for any signalling event
dbms_alert.waitone(name text, OUT message text, OUT status integer, n float8) Wait max. n seconds for name signalling event
dbms_alert.set_defaults Not implemented

The event is signalized at the operation escape, not after the operation finish as LISTEN/NOTIFY or original Oracle. What could be problem: a) false signalling - event is signalling but operation finish is not ensured (at the exception in AFTER TRIGGER), b) if the data change is signalling, this change has not to be visible to receiver (it is recommended to use function pq_sleep(n) after waitany or waitone functions - wait 0.1 to 1 sec according to system charge). Postponed operation could has another negative impact. Operations, which resend any event on one operation, can choke shared storage at the commit moment. Then you should raise pipe.h limit and transcribe library.

DBMS_OUTPUT

This package supports notifikation from PL/SQL in Oracle. There is RAISE NOTICE analogy in PostgreSQL. Contrary PostgreSQL it is server side solution, when scripts running on server perform session parameters. After script end client picks up content of parameters and figures them to service. PostgreSQL has notifikation solved on protocol level, where server sends asynchronous message to client, and client can display the message or not. If this packet would be used only for notification, then there will be no reason to involve it in this framework. Beside notification it can be used as (FIFO) front implementation in PL/pgSQL, because PL procedures can write and read this message front (message front can substitute temporary table).

  -- new session
  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);

  -- new sesion
  select dbms_output.serveroutput(true);
  select dbms_output.put_line('first_line');

The message signal reduction is implemented as in Oracle, i.e. if the same event with the same parameter is repeatedly signalized in one operation (the same signal), the repeated signals will be annuled and receiver will obtain only unique signals.

Functions

dbms_output.enable([buffer_size int4]) Start dbms_output support, elective buffer_size set up maximum buffer storage size
dbms_output.disable() Seactivate dbms_output support, put, put_line, new_line commands have no efect
dbms_output.serveroutput(bool) Start client output display demand, start dbms_output at the same time
dbms_output.put(text) Insert text in output buffer
dbms_output.put_line(text) Insert line (text with end of line symbol)
dbms_output.new_line() Insert end of line symbol
dbms_output.get_line(OUT line text, OUT status int4) Read one line from buffer. If the buffer is empty, status = 0 or status = 1
dbms_output.get_lines(OUT lines text[], INOUT numlines int4) Read line array from buffer. numlines parameter contain max. line number, then real line number

UTL_FILE

This package contains functions support of text files operation (read and write). To a certain degree C library style of stream (FILE) work is copied and encases this library more or less. Directories, where are files possible to read or write, are limited, that is why using library is safe at reasonable configuration. API closely respond to Oracle. If there are some differences, the reason is missing support of commiting reference parameters in PostgreSQL. Limits are identical. Max. 10 open files in one session. Text line can be max. 32K. After user logout PostgreSQL process ends and all open files are closed automatically. It is necessary to close files explicitly. If the function containing field deskriptor parameter is end, the possibility of file explicitly closing is lost. The only solution is to start utl_file.fclose_all() function, that will end all open files in session.

CREATE OR REPLACE FUNCTION read_file(loc text, filename text)
RETURNS SETOF text AS $$
DECLARE
  f utl_file.file_type;
BEGIN
  f := utl_file.fopen(loc, filename, 'r');
  LOOP
    RETURN NEXT utl_file.get_line(f);
  END LOOP;
EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    f := utl_file.fclose(f);
    RETURN;
  WHEN OTHERS THEN
    IF utl_file.is_open(f) THEN
      f := utl_file.fclose(f);
    END IF;
    RAISE EXCEPTION '%', SQLERRM;
END;
$$ LANGUAGE plpgsql VOLATILE;

Functions

utl_file.fclose(file utl_file.file_type) file utl_file.file_type Close the file assorted to file descriptor and return NULL. You'd better to set file descriptor parameter to NULL after this command to avoid using already-closed file descriptors.
utl_file.fclose_all() Close all open files in one session. After this command, all is_open calls against existing file descriptors return false.
utl_file.fflush(file utl_file.file_type) Ensure storing the buffer storage content on disc.
utl_file.fopen(directory text, filename text, mode text [, max_linesize int]) file utl_file.file_type Return open file descriptor. The file is possible to open in "r" read, "w" write and "a" append mode. In case of error evoke exception. This function enable access only to files in directories listed in utl_file.utl_file_dir table.
utl_file.get_line(file utl_file.file_type) text Read one line in text file. If the line is longer than adjusted limit (default 1KB), part of the line is returned. NO_DATA_FOUND exception evoke in case of empty file or if the file is whole read. Returned string does not contain the end of line symbol.
utl_file.get_nextline(file utl_file.file_type) text Similar to previous function. If you try to read from read file, return NULL.
utl_file.is_open(file utl_file.file_type) boolean Return true if the file descriptor is valid.
utl_file.new_line(file utl_file.file_type [, lines int]) Insert new line symbol to the file. It is elective how many symbols will be inserted.
utl_file.put(file utl_file.file_type, buffer text) Insert the buffer content to the file. Maximum length of line is supervised.
utl_file.put_line(file utl_file.file_type, buffer text) Add new line symbol behind inserted text.
utl_file.putf(file utl_file.file_type, format text, arg1..arg5 text) Insert formatted text. Formatted string can contain symbol %s, the content of n-th parameter substitute it. The number of symbols is limited to five.

Differences to Oracle

  • utl_file.get_line(file utl_file.file_type [, max_size integer]) .. return text in Oracle procedure
  • utl_file.get_nextline(file utl_file.file_type) .. the same as previous function, in EOF return NULL (get_line is ended by NO_DATA_FOUND exception)

DBMS_PIPE

Functions from dbms_pipe package enable asynchronous intersession communication. PostgreSQL has no analogical mechanism to enable intersession communication. The message can be empty or can contain random number of items (size of available shared memory limited). This packet functions can be used to testing (debugging report transfering), to aplication synchronization and also to the simulation of client-server architecture in PL/pgSQL aplications. The main differences in dbms_alert service, that also support intersession communication, are:

  • the message reduction does not happen,
  • we do not have to await the ending of operation,
  • the message will obtain only the first receiver,
  • the messages are transfer binary in appropriate data formats (if two different encoding sessions are communicate and you transfer the text, it is your bussiness to ensure appropriate code rewriting to receiver encoding)

Supported is pipe division to private and public, to explicitly created and default. With public pipe can operate anybody, with public only the founder. After sending message to unregistered pipe default pipe will be created automatically. It will be expired after exhausting. Explicit pipes are permanent (to explicit deleting or to server restart).

  -- Session A
  SELECT dbms_pipe.pack_message(CURRENT_DATE);
  SELECT dbms_pipe.pack_message('Servus, Rupert');
  SELECT dbms_pipe.send_message('boo',4,10);

  -- Session B
  SELECT dbms_pipe.receive_message('boo',4);
  SELECT dbms_pipe.unpack_message_date();
  SELECT dbms_pipe.unpack_message_text();

Oracle can work with parameters referential and it can overtax OUT function parameters. In PostgreSQL it is not possible, directly to the name of function (the record functions are exception) the output data type has to be obvious. That is why is original unpack_message function substitute by unpack_message_text, unpack_message_date, unpack_message_timestamp, unpack_message_number, unpack_message_bytea and unpack_message_record functions. It is possible to transfer data type record with defined type (see example).

  CREATE TYPE info AS (x integer, y integer);

  CREATE OR REPLACE FUNCTION send_info(x int, y int)
  RETURNS void AS $$
  DECLARE i info;
  BEGIN i.x := x; i.y := y;
    PERFORM dbms_pipe.pack_message(i);
    PERFORM dbms_pipe.send_message('info');
  END; 
  $$ LANGUAGE plpgsql;

  CREATE OR REPLACE FUNCTION get_info() 
  RETURNS info AS $$
  DECLARE i info;
  BEGIN
    PERFORM dbms_pipe.receive_message('boo');
    SELECT INTO i * FROM dbms_pipe.unpack_message_record() AS (x integer, y integer);
    RETURN i;
  END;
  $$ LANGUAGE plpgsql;

Differences to Oracle:

  • Pipe capacity restriction is not in byte but in number of messages
  • You can send zero timeout message
  • You can send empty message
  • next_item_type function can return TIMESTAMP (13) and RECORD(24)
  • Format RAW is not supported, there is support of BYTEA(23) instead

Functions

dbms_pipe.pack_message(value text|date|timestamp|numeric|record|bytea) Put content to local outgoing buffer.
dbms_pipe.unpack_message_text() text Get type text from local buffer.
dbms_pipe.unpack_message_date() date Get type date from local buffer.
dbms_pipe.unpack_message_timestamp() timestamp Get type timestamp from local buffer.
dbms_pipe.unpack_message_number() numeric Get type numeric from local buffer.
dbms_pipe.unpack_message_record() record Get type record from local buffer. Probably it will be necessary to implement type allocation (see example).
dbms_pipe.unpack_message_bytea() bytea Get type bytea from local buffer.
dbms_pipe.send_message(pipe text [,timeout int] [,limit int]) int Try to send message (local buffer content) to pipe pipe. If the faith pipe does not exist, will be created. If does not happen to send message in timeout return 1, otherwise 0. Timeout is in sec. Limit is in maximum message number, that pipe can retain. Possible is also 0. Default pipe is always public.
dbms_pipe.receive_message(pipe text [,timeout int]) int Try to get message from pipe. If does not success in timeout return 1, otherwise 0. Timeout is in sec (default 1 year)
dbms_pipe.create_pipe(pipe text, limit int [,private bool])
dbms_pipe.create_pipe(pipe text [,limit int])
Create explicit pipe. If pipe parameters are given, it will hold them up (limit of messages number, a private - limitation of access).
dbms_pipe.remove_pipe(pipe text) Despatch the pipe
dbms_pipe.reset_buffer() Reset of buffer starage (contain not sending or received packed values)
dbms_pipe.purge(pipe text) Empty pipe
dbms_pipe.next_item_type() int Get type (0 no next, 9 numeric, 11 text, 12 date, 13 timestamp, 23 bytea, 24 record)
dbms_pipe.unique_session_name() text Return unique session identifier in the form PG$PIPE$id$pid (id - process identifier using shared memory to pipe implementation, pid - process identifier)

View dbms_pipe.db_pipes

Given font statistics is available in View dbms_pipe.db_pipes.

  postgres=# select * from dbms_pipe.db_pipes;
   name | items | size | limit | private | owner
  ------+-------+------+-------+---------+-------
   boo  |     1 |   32 |       | f       |
  (1 row)

PLVdate

This function package is part of framework PLVision (probably the most extended PL/SQL function library, that does not come from Oracle). These functions enable to do calculations of calendar. Calendar can be easy modified, e.g. adapt to non-European world (Easter elimination, working Saturday, etc). For convenience the distribution contents calendars for Bohemian, all neighbour countries and some other. Our own holidays or spare-time days can be input in the calendar.


 SELECT plvdate.plvdate.default_holydays('czech');
 SELECT plvdate.add_bizdays(CURRENT_DATE, 10); 
 SELECT plvdate.isbizday('2006-12-25');
 SELECT plvdate.set_nonbizday('Friday');

Functions

add_bizdays(day date, days int) date Add n work days
plvdate.nearest_bizday(day date) date Return the nearest work day
plvdate.next_bizday(day date) date Return the next work day
plvdate.bizdays_between(day1 date, day2 date) int Return the number of work days between day1 and day2
plvdate.prev_bizday(day date) date Return the previous work day
plvdate.isbizday(date) bool Test if the day is work day
plvdate.set_nonbizday(dow varchar) Set day of the week as spare-time day
plvdate.unset_nonbizday(dow varchar) Set day of the year as work day
plvdate.set_nonbizday(day date) Nastaví den v roce jako mimopracovní
plvdate.unset_nonbizday(day date) Set day of the year as work day
plvdate.set_nonbizday(day date, repeat bool) Set day as spare-day, if repeat is true, day is taken as holiday
plvdate.use_easter() Calendar consider Easter
plvdate.unuse_easter(); Calendar does not consider Easter
plvdate.use_easter(useit boolean);
plvdate.using_easter() bool Return true, if calendar consider Easter
plvdate.include_start() bizdays_between consider the first day
plvdate.noinclude_start();
plvdate.include_start(include boolean);
plvdate.including_start() bool;
plvdate.plvdate.default_holydays(country text); Load the default calendar - czech, german, slovakia, ...

PLVstr string and PLVchr char functions

PLVision contains tens functions of string and char operations. orafce package contains only the more general, leave out e.g. parse source code support functions PL procedure. Without question the most interesting feature of implement functions is negative location support. If the negative number is given as location, the location will be referred to the end of string, not to the begin.

 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

Functions

plvstr.normalize(str text) White letters will be replaced by spaces, array of spaces wiil be replaced by one space.
plvstr.is_prefix(str text, prefix text, cs bool) Return true, if the prefix is string str. prefix. Parameter cs - case sensitive
plvstr.is_prefix(str text, prefix text) Return true, if the prefix is string str. prefix
plvstr.is_prefix(str int, prefix int) Return true, if the prefix is number str prefix
plvstr.is_prefix(str bigint, prefix bigint) Return true, if the prefix is number str prefix
plvstr.substr(str text, start int, len int) Return symbol length beginning of start position
plvstr.substr(str text, start int) Return string from the beginning to the end
plvstr.instr(str text, patt text, start int, nth int) Search template in string, what nth appearance
plvstr.instr(str text, patt text, start int) Search template in string
plvstr.instr(str text, patt text) Search template in string
plvstr.lpart(str text, div text, start int, nth int, all_if_notfound bool) Return letters on the left side from found pattern div, begin searching on start position, search nth pattern appearance, if all_if_notfound is true, return whole string after not founding pattern, otherwise return NULL
plvstr.lpart(str text, div text, start int, nth int) Return letters on the left side from found pattern div, begin searching on start position, search nth pattern appearance
plvstr.lpart(str text, div text, start int) Return letters on the right side from found pattern div, begin searching on start position
plvstr.lpart(str text, div text) Return letters on the left side from found pattern div
plvstr.rpart(str text, div text, start int, nth int, all_if_notfound bool) Return letters on the left side from found pattern div, begin searching on start position, search nth pattern appearance, if all_if_notfound is true, return whole string after not founding pattern, otherwise return NULL
plvstr.rpart(str text, div text, start int, nth int) Return letters on the left side from found pattern div, begin searching on start position, search nth pattern appearance
plvstr.rpart(str text, div text, start int) Return letters on the right side from found pattern div, begin searching on start position
plvstr.rpart(str text, div text) Return letters on the left side from found pattern div
plvstr.lstrip(str text, substr text, num int) Despatch n iteration of substr pattern on the left side of string
plvstr.lstrip(str text, substr text) Despatch repeated substr pattern on the left side of string
plvstr.rstrip(str text, substr text, num int) Despatch repeated substr pattern on the right side of string
plvstr.rstrip(str text, substr text) Despatch repeated substr pattern on the right side of string
plvstr.rvrs(str text, start int, _end int) Turn the part of string from start to _end round
plvstr.rvrs(str text, start int) Turn the part of string from start position to end round
plvstr.rvrs(str text) Turn string round
plvstr.left(str text, n int) Return first n letter from the left side. If n is negative, return first to n letters from the right side
plvstr.right(str text, n int) Return first n letter from the right side. If n is negative, return last to n letters from the left side
o plvchr.nth(str text, n int) Return n-th letter in string
plvchr.first(str text) Return the first letter
plvchr.last(str text) Return the last letter
plvchr.is_blank(c int) Is the letter empty?
plvchr.is_blank(c text) Is the letter empty?
plvchr.is_digit(c int) Is the letter number?
plvchr.is_digit(c text) Is the letter number?
plvchr.is_quote(c int) Is the letter apostroph?
plvchr.is_quote(c text) Is the letter apostroph?
plvchr.is_other(c int) Is it anything else, not ASCII letter
plvchr.is_other(c text) Is it anything else, not ASCII letter
plvchr.is_letter(c int) Is the letter char?
plvchr.is_letter(c text) Is the letter char?
plvchr.char_name(c text) Return the name of letter, interesting especially for white letters
plvchr.quoted1(str text) Insert text between
plvchr.quoted2(str text) Insert text between '"'
plvchr.stripped(str text, char_in text) Despatch char_in letters from string

PLVsubst

Functions from this package enable easy substitution some symbols in string. E.g. help conversion, error messages, log notes, etc. Basic substitute symbol is '%s'. This symbol can be changed within session. Data can be transmitted as array or as string containing the list of values separated by special character (default ',' ).

postgres=# select plvsubst.string('My name is %s %s', ARRAY['Pavel','Stěhule']);
          string           
---------------------------
 My name is Pavel Stěhule
(1 row)

Functions

plvsubst.setsubst ([varchar = '%s']) Set substitute symbol valid in the session
plvsubst.subst () Return valid substitute symbol
plvsubst.string (text, text[] [, text = '%s']) Substitute all substitute symbols by array members
plvsubst.string (text, text [, text = ',' [, text = '%s']]) Substitute all substitute symbols by list members

DBMS_UTILITY

Functions

dbms_utility.format_call_stack() Return multi-line string containing calling packet content.

PLVlex

Contain only one function, which returns table containing token set of given SQL string. code values can be different in various PostgreSQL versions.

postgres=# SELECT * FROM plvlex.tokens('SELECT t.* FROM tabulka t WHERE t.i = 10',true, true);
 pos |  token  | code |  class  | separator | mod  
-----+---------+------+---------+-----------+------
   0 | select  |  527 | KEYWORD |           | 
   7 | t       |      | IDENT   |           | 
   8 | .       |   46 | OTHERS  |           | self
   9 | *       |   42 | OTHERS  |           | self
  11 | from    |  377 | KEYWORD |           | 
  16 | tabulka |      | IDENT   |           | 
  24 | t       |      | IDENT   |           | 
  26 | where   |  591 | KEYWORD |           | 
  32 | t.i     |      | IDENT   |           | 
  36 | =       |   61 | OTHERS  |           | self
  38 | 10      |      | NCONST  |           | i
(11 rows)

Functions

plvlex.tokens (text, bool, bool) Implement lexical analysis of SQL string. The second argument establishes skipping white letters, the third argument designates if qualified identifiers will be generated.

DBMS_ASSERT

This package protect user input against SQL injection.

Functions

dbms_assert.enquote_literal(varchar) varchar Add leading and trailing quotes, verify that all single quotes are paired with adjacent single quotes.
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!
dbms_assert.noop(varchar) varchar Returns value without any checking.
dbms_assert.qualified_sql_name(varchar) varchar This function verifies that the input string is qualified SQL name.
dbms_assert.schema_name(varchar) varchar Function verifies that input string is an existing schema name.
dbms_assert.simple_sql_name(varchar) varchar This function verifies that the input string is simple SQL name.
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.

Functions

plunit.assert_true(bool [, varchar]) Asserts that the condition is true. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.assert_false(bool [, varchar]) Asserts that the condition is false. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.assert_null(anyelement [, varchar]) Asserts that the actual is null. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.assert_not_null(anyelement [, varchar]) Asserts that the actual isn't null. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.assert_equals(anyelement, anyelement [, double precision] [, varchar]) Asserts that expected and actual are equal. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed. Asserts that expected and actual are within the specified range. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.assert_not_equals(anyelement, anyelement [, double precision] [, varchar]) Asserts that expected and actual are equal. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed. Asserts that expected and actual are within the specified range. The optional message will be displayed if the assertion fails. If not supplied, a default message is displayed.
plunit.fail([varchar]) Fail can be used to cause a test procedure to fail immediately using the supplied message.

DBMS_RANDOM

Functions

dbms_random.initialize (int) Initialize package with a seed value.
dbms_random.normal () Returns random numbers in a standard normal distribution.
dbms_random.random () Returns random number from -2^31 .. 2^31.
dbms_random.seed (int), dbms_random.seed (text) Reset seed value.
dbms_random.string (opt text(1), len int) Create random string
dbms_random.terminate () Terminate package (do nothing in Pg)
dbms_random.value () Returns a random number from [0.0 - 1.0)
dbms_random.value (low double precision, high double precision) Returns a random number from [low - high)

Instalation

Presumption is to have PostgreSQL resource codes. To the directory contrib unpack orafce..tgz resource codes archive. Go to this directory from command line. Command make install will run compilation. Then restart PostgreSQL server. Run sql console psql above database, where you want to use Oracle functions, and import registration script orafunc.sql.

  cp orafce-2.0.1-preview.tgz /usr/loca/src/pgsql/contrib
  cd /usr/local/src/pgsql/contrib
  tar xvfz orafce-2.0.8-pre.tgz
  cd orafce
  make install
  /etc/init.d/postgres restart
  # make installcheck
  psql mojedb
  \i orafunc.sql
  select next_day(current_date,'saturday');

You can download rpm packages from http://developer.postgresql.org/~devrim/rpms/other/orafce/ (for Fedora 7 and similar systems).

Error reporting

If you discover an error in any function, please, send mail containing the error description and example, where are obvious error characteristics, to Pavel Stěhule

Others

If you find this package useful, send me a postcard to address:

 Pavel Stehule
 Skalice 12
 Benesov u Prahy
 256 01
 Czech Republic

Google AdSense