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