Oracle functionality (en)
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