PostgreSQL核心自帶的Oracle相容函式

標籤

PostgreSQL , Oracle , 相容函式 , Oracle compatible functions

---

背景

PostgreSQL功能上基本可以和Oracle對齊，只是一些語法或者函式名不一樣，所以為了做到相容，有一些相容包和相容函式的出現。

PostgreSQL自帶了一些Oracle相容函式，如果你覺得不夠意思，可以通過orafce外掛繼續擴充套件PostgreSQL與Oracle的相容性(本文不涉及orafce包)。

http://pgxn.org/dist/orafce/

http://api.pgxn.org/src/orafce/orafce-3.3.0/README.asciidoc

PostgreSQL自帶的Oracle相容函式

在原始碼src/backend/utils/adt/oracle_compat.c中可以找到它們的定義

/*-------------------------------------------------------------------------  
 * oracle_compat.c  
 *      Oracle compatible functions.  
 *  
 * Copyright (c) 1996-2016, PostgreSQL Global Development Group  
 *  
 *      Author: Edmund Mergl <E.Mergl@bawue.de>  
 *      Multibyte enhancement: Tatsuo Ishii <ishii@postgresql.org>  
 *  
 *  
 * IDENTIFICATION  
 *      src/backend/utils/adt/oracle_compat.c  
 *  
 *-------------------------------------------------------------------------  
 */  

/********************************************************************  
 *  
 * lower  
 *  
 * Syntax:  
 *  
 *       text lower(text string)  
 *  
 * Purpose:  
 *  
 *       Returns string, with all letters forced to lowercase.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * upper  
 *  
 * Syntax:  
 *  
 *       text upper(text string)  
 *  
 * Purpose:  
 *  
 *       Returns string, with all letters forced to uppercase.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * initcap  
 *  
 * Syntax:  
 *  
 *       text initcap(text string)  
 *  
 * Purpose:  
 *  
 *       Returns string, with first letter of each word in uppercase, all  
 *       other letters in lowercase. A word is defined as a sequence of  
 *       alphanumeric characters, delimited by non-alphanumeric  
 *       characters.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * lpad  
 *  
 * Syntax:  
 *  
 *       text lpad(text string1, int4 len, text string2)  
 *  
 * Purpose:  
 *  
 *       Returns string1, left-padded to length len with the sequence of  
 *       characters in string2.  If len is less than the length of string1,  
 *       instead truncate (on the right) to len.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * rpad  
 *  
 * Syntax:  
 *  
 *       text rpad(text string1, int4 len, text string2)  
 *  
 * Purpose:  
 *  
 *       Returns string1, right-padded to length len with the sequence of  
 *       characters in string2.  If len is less than the length of string1,  
 *       instead truncate (on the right) to len.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * btrim  
 *  
 * Syntax:  
 *  
 *       text btrim(text string, text set)  
 *  
 * Purpose:  
 *  
 *       Returns string with characters removed from the front and back  
 *       up to the first character not in set.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * btrim1 --- btrim with set fixed as ` `  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * byteatrim  
 *  
 * Syntax:  
 *  
 *       bytea byteatrim(byta string, bytea set)  
 *  
 * Purpose:  
 *  
 *       Returns string with characters removed from the front and back  
 *       up to the first character not in set.  
 *  
 * Cloned from btrim and modified as required.  
 ********************************************************************/  

/********************************************************************  
 *  
 * ltrim  
 *  
 * Syntax:  
 *  
 *       text ltrim(text string, text set)  
 *  
 * Purpose:  
 *  
 *       Returns string with initial characters removed up to the first  
 *       character not in set.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * ltrim1 --- ltrim with set fixed as ` `  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * rtrim  
 *  
 * Syntax:  
 *  
 *       text rtrim(text string, text set)  
 *  
 * Purpose:  
 *  
 *       Returns string with final characters removed after the last  
 *       character not in set.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * rtrim1 --- rtrim with set fixed as ` `  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * translate  
 *  
 * Syntax:  
 *  
 *       text translate(text string, text from, text to)  
 *  
 * Purpose:  
 *  
 *       Returns string after replacing all occurrences of characters in from  
 *       with the corresponding character in to.  If from is longer than to,  
 *       occurrences of the extra characters in from are deleted.  
 *       Improved by Edwin Ramirez <ramirez@doc.mssm.edu>.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * ascii  
 *  
 * Syntax:  
 *  
 *       int ascii(text string)  
 *  
 * Purpose:  
 *  
 *       Returns the decimal representation of the first character from  
 *       string.  
 *       If the string is empty we return 0.  
 *       If the database encoding is UTF8, we return the Unicode codepoint.  
 *       If the database encoding is any other multi-byte encoding, we  
 *       return the value of the first byte if it is an ASCII character  
 *       (range 1 .. 127), or raise an error.  
 *       For all other encodings we return the value of the first byte,  
 *       (range 1..255).  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * chr  
 *  
 * Syntax:  
 *  
 *       text chr(int val)  
 *  
 * Purpose:  
 *  
 *      Returns the character having the binary equivalent to val.  
 *  
 * For UTF8 we treat the argumwent as a Unicode code point.  
 * For other multi-byte encodings we raise an error for arguments  
 * outside the strict ASCII range (1..127).  
 *  
 * It`s important that we don`t ever return a value that is not valid  
 * in the database encoding, so that this doesn`t become a way for  
 * invalid data to enter the database.  
 *  
 ********************************************************************/  

/********************************************************************  
 *  
 * repeat  
 *  
 * Syntax:  
 *  
 *       text repeat(text string, int val)  
 *  
 * Purpose:  
 *  
 *      Repeat string by val.  
 *  
 ********************************************************************/  

參考

src/backend/utils/adt/oracle_compat.c

http://pgxn.org/dist/orafce/

http://api.pgxn.org/src/orafce/orafce-3.3.0/README.asciidoc