TAG 21
作者
digoal
日期
2017-02-07
标签
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
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
