作者:邓琼
就职于中电福富信息科技有限公司,中国首批"PostgreSQL ACE伙伴"。主导并参与某省电信公司Oracle到PG的迁移工作,长期致力于电信行业的PostgreSQL技术推广工作。
一、Postgres限制
Limit | Value |
Maximum Database Size | Unlimited |
Maximum Table Size | 32TB |
Maximum Row Size | 1.6TB |
Maximum Field Size | 1GB |
Maximum Rows/Table | Unlimited |
Maximum Columns/Table | 250~1600 |
Maximum Indexes/Table | Unlimited |
DBA的概念映射
Postgres 的架构和 Oracle的架构对比
MVCC的原理实现有差异
1、oracle 采用 rollback segment 的方式实现
2、Postgres 采用事务 id xmin xmax 的方式实现
3、SGA-> shared_buffers
4、PGA-> work_mem
5、PMON-> postmaster
6、TNS Listener->postmaster
7、grant/revoke -> 几乎一样的语法
二、数据类型映射
2.1字段类型
ORACLE字段类型 | PG字段类型 | 描述 |
Varchar 、 varchar2 、 nvarchar 、nvarchar2 | char, varchar, text | |
char nchar | char, varchar, text | |
cblog, long | Varchar,text,jsonb | |
Number | Bigint,int,small,real,double presion:性能很好,精度不好控制 Numeric:精度很高,性能略差 | |
Binary_integer,binary_float, BINARY_DOUBLE | Integer,float,numeric | |
Blob,raw,log_raw | Bytea如果大对象是 json 可以换做 jsonb | |
Date | Date or timestamp Timestamp with timezone | |
Date 加 减 | Date + inteval ‘ N day/minute’ | |
Nls_date_format | To_char to_date | |
TIMESTAMP | date,timestamp, timestamptz, char,varchar, text | |
TIMESTAMP WITH TIME ZONE | date,timestamp,timestamptz, char, varchar, text | |
TIMESTAMP WITH LOCAL TIME ZONE | date,timestamp,timestamptz, char, varchar, text | |
INTERVAL YEAR TO MONTH | interval, char, varchar, text | |
INTERVAL DAY TO SECOND | interval, char, varchar, text | |
MDSYS.SDO_GEOMETRY | geometry(see"PostGIS support") |
2.2转化关系
项目 | Oracle | Postgres |
当前时间 | SYSDATE | now(),clock_timestamp(),current_time,current_date,current_time,cu rrent_timestamp,localtime,localtimestamp |
序列 | SEQNAME.NEXTVAL | NEXTVAL('SEQNAME') |
固定值列 | SELECT '1' AS COL1 | SELECT CAST('1' AS TEXT) as col |
NVL | NVL 函数 | 用 COALESCE 函数替换 |
INSTR 函数 | instr('str1','str2') | strpos('str1','str2') |
外连接 | Oracle 可简写为(+) | 用 LEFT JOIN 等语句替换 |
层次查询 | START WITH 语句 CONNECT BY 语句 | 用 WITH RECURSIVE 语句 |
数据库对象大 小写 | 统一大写,””包起来的除外 | 统一小写,””包起来的除外 |
GOTO 语句 | GOTO 语句 | pgsql 不支持 |
同义词 | Oracle 支持同义词 | 用视图代替 |
trunc | trunc(时间) | date_trunc() |
DUAL | SELECT 1+1 FROM DUAL | SELECT 1+1或者CREATE VIEW dual AS SELECT current_timestamp |
ROWNUM | ROWNUM 关键字 | 两种情况: 1.限制结果集数量,用于翻页等:SELECT * FROM T LIMIT 5 OFFSET 0 2. 生 成 行 号 :ROW_NUMBER()OVER() |
DECODE 等判 断函数 | DECODE() | 用标准的 CASE WHEN THEN ELSE END 语句替换 |
TO_CHAR | TO_CHAR(COL,FMT),格式化字 符串可以为空 | TO_CHAR(COL1,'FM999999'),9 的个数为字段长度,详细定义见: https://www.postgresql.org/docs/10/static/functions-formatting.html |
TO_NUMBER | TO_NUMBER(COL,FMT),格式 化字符串可以为空 | TO_NUMBER(COL1,'999999'),9 的个数为字段长度,详细定义见: http://www.postgresql.org/docs/10/static/functions-formatting.html |
NULL 和'' | ORACLE 认 为 '' 等 同 于 NULL,'a'||null 结果是'a' | NULL 和''不同,'a'||null 结果是 null,用 concat()函数替代 |
NULL 和'' | LENGTH('')为 NULL | LENGTH('')为 0 |
NULL 和'' | TO_DATE('','YYYYMMDD')为空 | TO_DATE('','YYYYMMDD')为 0001-01-01 BC |
NULL 和'' | TO_NUMBER('',1)为NULL | TO_NUMBER('',1),报错 |
ADD_MONTH S | ADD_MONTHS(DATE,INT) | CREATE FUNCTION add_months(date, int) RETURNS date AS 'SELECT ($1 +($2::text||'' month'')::interval)::date' LANGUAGE 'sql' 或 SQL:SELECT ($1 +($2::text||' month')::interval) |
LAST_DAY | LAST_DAY(DATE) | 创建函数来解决 CREATE OR REPLACE FUNCTION last_day(date) RETURNS date AS $$ SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date; $$ LANGUAGE 'sql'; 或 SQL: SELECT (date_trunc('MONTH', $1) + interval '1 month - 1 day')::date; |
MONTHS_BE TWEEN | MONTHS_BETWEEN(DATE,DA TE) | 创建函数来解决 CREATE FUNCTION MONTH_BETWEEN (d1 timestamp,d2 timestamp) RETURNS NUMERIC AS 'SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer' LANGUAGE 'sql'; |
BITAND | BITAND(A,B) | A & B |
MINUS | MINUS 语句 | 以 EXCEPT 语句来替代 |
BIN_ | SELECT BIN_TO_NUM(1,0,1,0) AS VALUE1 FROM DUAL | SELECT CAST(B'1010' AS INTEGER) AS VALUE1 |
UPDATE 语 句 列列表 | UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name,first_name FROM salesmen WHERE salesmen.id =accounts.sales_id); | UPDATE accounts a SET contact_last_name=blast_name, contact_first_name=b.first_name From salesmen b b.id =a.sales_id); |
SUBSTR 函数 | 如果从第一个开始取子串,可以从 0 开始,也可以从 1 开始,如 果不是第一个开始,则从 1 开始 计数,可以为负值,从字符串结尾计数,用于取最后几位。 | 从 1 开始计数。如果要取最后几位,可以用 RIGHT 函数解决 |
子查询别名 | 子查询别名 | 必须有别名 |
列(别)名为关键字 | Oracle 中比如 name,type 这样的关键字可以直接作为列的别 名,比如:select xx name from t | 需要加 as,比如 select xx as name from t |
当前登录用户 | SELECT USER FROM DUAL | select current_user |
ALL_COL_CO MMENTS | 通过 SELECT * FROM ALL_COL_COMMENTS 可以获得列注释信息 | select s.column_name as COLUMN_NAME, coalesce(col_description(c.oid,ordinal_position) ,s.column_name) as COMMENTS from information_schema.columns s,pg_class c where s.table_name = 'ac01_si' and s.table_name = c.relname and s.table_schema = current_schema() PG 需要通过 col_description 获得列注释信息 |
修改表字段类型 | 1.如果字段无数据,可直接修改 2.如果有数据且新类型和原类型兼容,也可以直接修改 3.如果不兼容,可通过对原字段改名,然后增加新字段,再通过 UPDATE 语句对数据进行处理 | 1.如果新类型和原类型兼容,可直接修改 2.如果不兼容,需要使用 USING 关键字然后提供一个类型转换的表达式 |
储存过程函数包 | Function,procedure package | pgsql 不支持 procedure 和 package , 都需要改写成 function, 当 package 有全局变量的情况修改起来比较麻烦,我们是用临时表传递 的。 |
cursor 的属性 | %FOUND %NOTFOUND %ISOPEN %ROWCOUNT | %FOUND → found %NOTFOUND → not found %ISOPEN → pgsql 不 支 持 %ROWCOUNT → pgsql 不支持另外关于 cursor 还发现了其他差异 |
3. O与P对象说明
3.1Schema
Oracle是按照每个用户为独立的schema,postgres是可以独立创建schema,和用户无关
3.2标识符
-
Schema、表、列、函数、视图...
-
oracle的是大写,除非是双引号括起来
-
Postgres统一转换为小写,除非是双引号括起来关键还是要保持一致
3.3表
创建表一般都兼容,除了Global temporay table使用local temp表分区表使用inherent trigger rule和check constraint pg_pathman Initrans,maxextents存储参数删除他们Pctfree:使用fillfactor填充子
3.4列
虚拟列:使用视图
数据类型:根据类型映射
3.5Constraint
主键、外键、唯一键、条件约束、非空约束都支持索引:
Btree/descending/ascending:pg都支持
Reverse key/bitmap/join:pg没实现
Partition:Hash、List、range:都兼容 pg_pathman或触发器实现pg10自带分区功能
Tablespace:原理不一样,但工作的效果是一的
三、对象转化
3.1存储过程转化
-
return改为returns
-
Execute immediate改为execute
-
select没有into该为perform
-
选择一种储存过程语言
create or replace function fn(a inout) returns int as $$ declare ... begin ... end;$$language;
-
%type,%rowtype:能正常功能
-
cursor_name%rowtype:不工作,使用为类型record
-
refcursors:没有替代方案,使用returning特性
-
匿名块:Postrges不支持
-
在事务中commit/rollback, pg11支持事务自治
-
reverse loop:可以采用调换start/end的条件解决For i in reverse 1..10 loop For i in reverse 10..1 loop
3.2触发器转化
改写为出发函数和触发器的方式解决
Create or replace function trg_fn() returns trigger as $$ ... $$ language xx;
Create trigger tbl_trg before update on table execute procedure trg_fn();
:NEW,:OLD
代表触发器使用时捕获的新值和旧值Updating,insert ->通过TG_OP;TG_*等变量获取在 before trigger记得返回return NEW;
条件触发器:
oracle达到某个条件才执行触发器;
pg可以采用事件触发器
参考:https://www.postgresql.org/docs/11/static/plpgsql-trigger.html
3.3储存过程
postgres只有函数,采用returns void的返回值
Oracle postrges储存过程迁移注意事项:
-
如果一个SQL命令中使用的名字可能是一个表的列名或者是对一个函数中变量的引用, 那么PL/SQL会将它当作一个列名
-
在PostgreSQL中,函数体必须写成字符串文本。因此你需要使用美元符引用或者转义函数体中的单引号
-
数据类型名称常常需要翻译
-
应该用模式把函数组织成不同的分组,而不是用包
-
因为没有包,所以也没有包级别的变量。可以在临时表里保存会话级别的状态
-
带有REVERSE的整数FOR循环的工作方式不同:PL/SQL中是从第二个数向第一个数倒数,而PL/pgSQL是从第一个数向第二个数倒数,因此在移植时需要交换循环边界
-
查询上的FOR循环(不是游标)的工作方式同样不同:目标变量必须已经被声明,而PL/SQL总是会隐式地声明它们。但是这样做的优点是在退出循环后,变量值仍然可以访问
-
在使用游标变量方面,存在一些记法差异
3.4函数
-
Return改为returns
-
对于函数的空参数,需要提供双括号():Create function fn() returns ...
-
默认值default ,postgres支持
-
可以返回为类型record,但是调用者需要知道列的名字
-
可以返回set of record: returns setof type oracle有table functions
3.5package
-
一组变量,函数和储存过程
-
采用schema对函数分组
-
使用(临时)表替换包内的变量
-
对于private函数和变量,没有替代方案
-
包的初始代码,可以在每次调用函数调用一个初始函数
-
local function函数里面递归调用函数postgres不支持,采用正常的函数替换
3.6synonyms
postgres不支持这个特性采用视图解决或包装成函数
3.7database links
-
不支持这个特性
-
采用dblink插件和视图解决
3.8connect by
采用with recursive by改写
3.9物化视图
Postgres支持
3.10分区
可以采用inherent触发器规则条件约束和constraint_exlusion pg_pathman来解决
3.11sequence序列
-
和oracle一样的机制
-
nocache改为cache 1(或者 remove 这个参数)
-
maxvalue9999999999999999999999999
减少限制最大9223372036854775807
-
next,.currval
nextval(‘sequence’)
-
order/noorder oracle需要这个做cluster/rac的设置Postgres没有
-
no{cache|minvalue|maxvalue|cycle}通no{*} 代替nominvalue改为minvalue
3.12关联语法
Postgres提供{left|right|full|out} join oracle也提供
3.13集合操作
UNION并集
INTERSECT交集
EXCEPT差集
3.14使用参数名进行函数调用
=>改为 :=var = fn(c=>10,a=>’xyz’,b=>2.5)
改为 var = fn(c := 10,a :=’xyz’,b:=2.5)
3.15Dual
Orafce兼容oracle相关函数
PostgreSQL是和Oracle最接近的企业数据库,包括数据类型,功能,架构和语法等几个方面。甚至大多数的日常应用的性能也不会输给Oracle。但是Oracle有些函数或者包,默认PostgreSQL是没有的,需要安装orafce包来实现这些兼容性。
例如现在orafce已经包含了如下内容:
-
类型date, varchar2 and nvarchar2
-
函数concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh and oracle.substr
-
dual表
-
package :
Dbms_alert
Dbms_pipe
Utl_file
Dbms_output
Dbms_random
Date
operations
Dual
To_char() 支持多不同的数据类型(需要安装插件包,虽然可以兼容,但更建议直接改写,减少依赖)orafce的安装步骤如下:http://pgxn.org/dist/orafce/
3.16Rownum
Row_number()窗口函数
详情参考:https://www.postgresql.org/docs/11/static/functions-window.html
3.17rowid
使用ctid系统列不能用作分区键,空间回收ctid会变化使用oid列。
四、迁移方案
迁移方案参考Ora2pg工具迁移
https://github.com/darold/ora2pg
http://ora2pg.darold.net/documentation.html
具体的迁移步骤省略
五、O转P函数兼容
5.1Connect by
PostgreSQL实现Oracle的connect_by with实现层级数据的查询
PostgreSQL不支持Oracle中的connect by语法,而即使是edb对connect by的兼容有限,edb 不支持的点:
-
函数sys_connect_by_path
-
在SELECT表达式中使用PRIOR限定符
-
CONNECT BY有多个表达式
-
CONNECT_BY_ROOT表达式
Oracle 中还有以下:
connect_by_is_leaf : connect_by_isleaf IS a new operator that comes WITH Oracle 10 g
AND enhances the ability TO perform hierarchical queries.connect_by_iscycle : connect_by_is_cycle IS a new operator that comes WITH Oracle 10 g
AND enhances the ability TO perform hierarchical queries.
解决方案如下:
假设Oracle中有表:
CREATE TABLE sys_cbp_test
EGER NOT NULL PRIMARY KEY,parent_id INTEGER);
生成测试数据:
表的内容如下:
SQL> select * from sys_cbp_test;
ID PARENT_ID
--- ---------- 1
2 1
3 2
4 3
5 1
6 5
7 2
20
21 20
22 21
10 rows selected.
Oracle 中的查询语句为:
SELECT id,
prior id, parent_id, Level,
sys_connect_by_path (TO_CHAR (id), '/') AS Path,
CONNECT_BY_ROOT id AS root FROM sys_cbp_test
START WITH parent_id IS NULL CONNECT BY prior id = parent_id;
Oracle 中查询的结果如下:
ID PRIORID PARENT_ID LEVEL PATH ROOT
--- ---------- ---------- ---------- ------------ ----------
1 1 1 1
2 1 1 2 1/2 1
3 2 2 3 1/2/3 1
4 3 3 4 1/2/3/4 1
7 2 2 3 1/2/7 1
5 1 1 2 1/5 1
6 5 5 3 1/5/6 1
20 1 20 20
21 20 20 2 20/21 20
22 21 21 3 20/21/22 20
10 rows selected.
PostgreSQL 中生成测试数据的 SQL 如下:
INSERT INTO sys_cbp_test VALUES (1, NULL),(2, 1),(3, 2),(4, 3),(5, 1),(6, 5),(7, 2),(20,NULL),(21, 20),(22, 21);
PostgreSQL 中的 SQL 如下:
WITH RECURSIVE x (id, prior_id, parent_id, level, path, root) AS
(SELECT id, NULL::INT AS prior_id, NULL::INT AS parent_id, 1, array[id], id as root FROM sys_cbp_test
WHERE parent_id IS NULL UNION ALL
SELECT b.id, x.id AS prior_id, b.parent_id, level+1, x.path|| b.id, x.root FROM x, sys_cbp_test b
WHERE x.id = b.parent_id
)
SELECT id, prior_id, parent_id, level, '/'|| array_to_string (path, '/') AS path, root FROM x;
PostgreSQL 看到的结果如下:
id | prior_id | parent_id | level | path | root
----+----------+-----------+-------+-----------+------
1 | | | 1 | 1 | 1
20 | | | 1 | 20 | 20
2 | 1 | 1 | 2 | 1/2 | 1
5 | 1 | 1 | 2 | 1/5 | 1
21 | 20 | 20 | 2 | 20/21 | 20
3 | 2 | 2 | 3 | 1/2/3 | 1
6 | 5 | 5 | 3 | 1/5/6 | 1
7 | 2 | 2 | 3 | 1/2/7 | 1
22 | 21 | 21 | 3 | 20/21/22 | 20
4 | 3 | 3 | 4 | 1/2/3/4 | 1
(10 rows)
行的顺序与 Oracle 不一样,但对于关系型数据库一般是不保证行的顺序的,如果要保证, 需要排序,如:
WITH RECURSIVE x (id, prior_id, parent_id, level, path, root) AS
SELECT id, NULL::INT AS prior_id, NULL::INT AS parent_id, 1, array[id], id as root FROM sys_cbp_test
WHERE parent_id IS NULL UNION ALL
SELECT b.id, x.id AS prior_id, b.parent_id, level+1, x.path|| b.id, x.root FROM x, sys_cbp_test b
WHERE x.id = b.parent_id
)
SELECT id, prior_id, parent_id, level, '/'|| array_to_string (path, '/') AS path, root FROM x
ORDER BY id NULLS FIRST;
id | prior_id | parent_id | level | path | root
----+----------+-----------+-------+-----------+------
1 | | | 1 | 1 | 1
2 | 1 | 1 | 2 | 1/2 | 1
3 | 2 | 2 | 3 | 1/2/3 | 1
4 | 3 | 3 | 4 | 1/2/3/4 | 1
5 | 1 | 1 | 2 | 1/5 | 1
6 | 5 | 5 | 3 | 1/5/6 | 1
7 | 2 | 2 | 3 | 1/2/7 | 1
20 | | 1 | 20 | 20
21 | 20 | 20 | 2 | 20/21 20
22 | 21 | 21 | 3 | 20/21/22 | 20
(10 rows)
5.2ratio
Oracle的分析函数RATIO_TO_REPORT()是用于计算当前值在分组内的占比的
RATIO_TO_REPORT is an analytic function. It computes the ratio of a value
to the sum of a set of values.
expr evaluates to null, then the ratio-to-report value also evaluates to null.
PostgreSQL也支持窗口查询,但是没有提供这个分析函数,不过我们知道它是干什么的, 当然就知道如何写SQL来实现同样的目的了。Oracle例子:
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME SALARY RR
------------------------- ---------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
drop table t1; create table t1( id serial not null,
val integer not null,
category character varying(1)
);
insert into t1(val,category) values(10,'a'),(10,'a'),(20,'a'),(20,'b');
select id,val,1.0*val/nullif(sum(val) over(),0) as ratio_to_report from t1; 1;10;0.16666666666666666667
2;10;0.16666666666666666667
3;20;0.33333333333333333333
4;20;0.33333333333333333333
select id,val,category,1.0*val/nullif(sum(val) over(partition by category),0) as ratio_to_report from t1;
1;10;"a";0.25000000000000000000
2;10;"a";0.25000000000000000000
3;20;"a";0.50000000000000000000
4;20;"b";1.00000000000000000000
5.3eval
decode
Oracle:
SELECT product_id,
DECODE (warehouse_id, 1, ’Southlake’, 2, ’San Francisco’,
3, ’New Jersey’,
4, ’Seattle’,
’Non-domestic’) quantity_on_hand FROM inventories
Postgres
SELECT a,
CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'
ELSE 'other' END
FROM test
5.4Insert all
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME SALARY RR
------------------------- ---------- ----------
Khoo 3100 .223021583
Baida 2900 .208633094
Tobias 2800 .201438849
Himuro 2600 .18705036
Colmenares 2500 .179856115
drop table t1; create table t1( id serial not null,
val integer not null,
category character varying(1)
);
insert into t1(val,category) values(10,'a'),(10,'a'),(20,'a'),(20,'b');
select id,val,1.0*val/nullif(sum(val) over(),0) as ratio_to_report from t1; 1;10;0.16666666666666666667
2;10;0.16666666666666666667
3;20;0.33333333333333333333
4;20;0.33333333333333333333
select id,val,category,1.0*val/nullif(sum(val) over(partition by category),0) as ratio_to_report from t1;
1;10;"a";0.25000000000000000000
2;10;"a";0.25000000000000000000
3;20;"a";0.50000000000000000000
4;20;"b";1.00000000000000000000
5.5instr
instr函数模仿Oracle的对应函数
-
语法:instr(string1, string2, [n], [m])其中[]表示可选参数
-
从第n个字符开始搜索 string2在string1中的第m次出现。如果n是负的,反向搜索
-
如果m没有被传递,假定为1(从第一个字符开始搜索)
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$ DECLARE
pos integer; BEGIN
pos:= instr($1, $2, 1); RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer) RETURNS integer AS $$
DECLARE
pos integer NOT NULL DEFAULT 0; temp_str varchar;
beg integer; length integer; ss_length integer;
BEGIN
IF beg_index > 0 THEN
temp_str := substring(string FROM beg_index); pos := position(string_to_search IN temp_str);
IF pos = 0 THEN RETURN 0;
ELSE
RETURN pos + beg_index - 1; END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search); length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str);
IF pos > 0 THEN RETURN beg;
END IF;
beg := beg - 1; END LOOP;
RETURN 0; ELSE
RETURN 0; END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search varchar,
beg_index integer, occur_index integer)
RETURNS integer AS $$ DECLARE
pos integer NOT NULL DEFAULT 0; occur_number integer NOT NULL DEFAULT 0; temp_str varchar;
beg integer; i integer;
length integer; ss_length integer;
BEGIN
IF beg_index > 0 THEN beg := beg_index;
temp_str := substring(string FROM beg_index);
FOR i IN 1..occur_index LOOP
pos := position(string_to_search IN temp_str);
IF i = 1 THEN
beg := beg + pos - 1; ELSE
beg := beg + pos; END IF;
temp_str := substring(string FROM beg + 1); END LOOP;
IF pos = 0 THEN RETURN 0;
ELSE
RETURN beg; END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search); length := char_length(string);
beg := length + beg_index - ss_length + 2;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length); pos := position(string_to_search IN temp_str);
IF pos > 0 THEN
occur_number := occur_number + 1;
IF occur_number = occur_index THEN RETURN beg;
END IF; END IF;
beg := beg - 1; END LOOP;
RETURN 0; ELSE
RETURN 0; END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
5.6 rownum
Oracle ROWNUM是一个虚拟列,每输出一行递增 1.Oracle rownum
通常被用于LIMIT输出记录数。
SELECT ROWNUM, empno, ename, job FROM emp WHERE ROWNUM < 5 ORDER BY
ename;
rownum | empno | ename | job
--------+-------+-------+----------
2 | 7499 | ALLEN | SALESMAN
4 | 7566 | JONES | MANAGER
1 | 7369 | SMITH | CLERK
3 | 7521 | WARD | SALESMAN
(4 rows)
或者用于生成序列值。
ALTER TABLE jobhist ADD seqno NUMBER(3); UPDATE jobhist SET seqno = ROWNUM;
SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS start, job FROM jobhist;
seqno | empno | start | job
-------+-------+-----------+----------- 1 | 7369 | 17-DEC-80 | CLERK
2 | 7499 | 20-FEB-81 | SALESMAN
3 |7521 | 22-FEB-81 | SALESMAN
4 | 7566 | 02-APR-81 | MANAGER
5 |7654 | 28-SEP-81 | SALESMAN
6 |7698 | 01-MAY-81 | MANAGER
7 | 7782 | 09-JUN-81 | MANAGER
8 | 7788 | 19-APR-87 | CLERK
9 | 7788 | 13-APR-88 | CLERK
10 | 7788 | 05-MAY-90 | ANALYST
11 | 7839 | 17-NOV-81 | PRESIDENT
12 | 7844 | 08-SEP-81 | SALESMAN
13 | 7876 | 23-MAY-87 | CLERK
14 | 7900 | 03-DEC-81 | CLERK
15 | 7900 | 15-JAN-83 | CLERK
16 | 7902 | 03-DEC-81 | ANALYST
17 | 7934 | 23-JAN-82 | CLERK
(17 rows)
PostgreSQL
rownum
PostgreSQL目前没有rownum虚拟列,但是实现同样的功能确很容易:
1、输出行号,使用临时序列
postgres=# create temp sequence if not exists tmp_seq; postgres=# alter sequence tmp_seq restart with 1;
postgres=# select nextval('tmp_seq') as rownum, * from test limit 10; rownum | id | info | crt_time
--------+----+------+----------------------------
1 | 1 | test | 2018-01-24 11:06:24.882708
2 | 2 | test | 2018-01-24 11:06:24.882708
3 | 3 | test | 2018-01-24 11:06:24.882708
4 | 4 | test | 2018-01-24 11:06:24.882708
5 | 5 | test | 2018-01-24 11:06:24.882708
6 | 6 | test | 2018-01-24 11:06:24.882708
7 | 7 | test | 2018-01-24 11:06:24.882708
8 | 8 | test | 2018-01-24 11:06:24.882708
9 | 9 | test | 2018-01-24 11:06:24.882708
10 | 10 | test | 2018-01-24 11:06:24.882708
(10 rows)
2、输出行号,使用窗口函数
postgres=# select row_number() over () as rownum, * from test limit 10; rownum | id | info | crt_time
--------+----+------+----------------------------
1 | 1 | test | 2018-01-24 11:06:24.882708
2 | 2 | test | 2018-01-24 11:06:24.882708
3 | 3 | test | 2018-01-24 11:06:24.882708
4 | 4 | test | 2018-01-24 11:06:24.882708
5 | 5 | test | 2018-01-24 11:06:24.882708
6 | 6 | test | 2018-01-24 11:06:24.882708
7 | 7 | test | 2018-01-24 11:06:24.882708
8 | 8 | test | 2018-01-24 11:06:24.882708
9 | 9 | test | 2018-01-24 11:06:24.882708
10 | 10 | test | 2018-01-24 11:06:24.882708
(10 rows)
3、LIMIT,直接语法支持
postgres=# select * from test limit 10; id | info | crt_time
----+------+----------------------------
1 | test | 2018-01-24 11:06:24.882708
2 | test | 2018-01-24 11:06:24.882708
3 | test | 2018-01-24 11:06:24.882708
4 | test | 2018-01-24 11:06:24.882708
5 | test | 2018-01-24 11:06:24.882708
6 | test | 2018-01-24 11:06:24.882708
7 | test | 2018-01-24 11:06:24.882708
8 | test | 2018-01-24 11:06:24.882708
9 | test | 2018-01-24 11:06:24.882708
10 | test | 2018-01-24 11:06:24.882708
(10 rows)
5.7synonym匿名
匿名语法如下:
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
[ schema. ]synonym
FOR [ schema. ]object [ @ dblink ] ;
参考:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm
5.7.1表
代码写死了b.tbl123:
create table a.tbl(id int);
通过视图,create view b.tbl123 as select * from a.tbl;
这种简单视图,支持增删改查,和直接使用a.tbl是一样的。相当于建立了a.tbl的b.tbl123匿名。
通过search_path,如果对象名没变,只是在不同的schema下,使用search_path是最通用的方法:
set search_path=a,"$user",public;
那么会先访问a这个schema下的对象。
5.7.2函数
代码写死了b.fun123:
create or replace function a.fun(int) returns int as $$
....
$$ language plpgsql strict;
通过函数嵌套,
create or replace function b.fun123(int) returns int as $$ select a.fun($1);
language sql strict;
通过search_path,与1类似。
5.7.3视图
通过视图,与1类似。通过search_path
5.7.4物化视图
通过视图,与1类似。
通过search_path,与1类似。
5.7.5DBLINK
通过重定义一样的dblink。
通过search_path,与1类似。
5.7.6外部表
通过视图,与1类似。
通过search_path,与1类似。
5.7.7自定义类型
通过重定义一样的类型。
通过search_path,与1类似。
5.8order by INT position
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias } [ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
按表达式、列别名、select位置排序。
PostgreSQL也支持这种语法
PostgreSQL order by支持
PostgreSQL天然支持order by[字段、表达式、位置]
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias } [ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
例子
1、按别名排序
postgres=# explain select relpages as ooo,* from pg_class order by ooo;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=71.81..73.32 rows=602 width=737) Sort Key: relpages
-> Seq Scan on pg_class (cost=0.00..44.02 rows=602 width=737) (3 rows)
2、按SELECT中的位置排序
postgres=# explain select relpages,reltuples,relname from pg_class order by 2 limit 1; QUERY PLAN
------------------------------------------------------------------------
Limit (cost=47.03..47.03 rows=1 width=72)
-> Sort (cost=47.03..48.54 rows=602 width=72) Sort Key: reltuples
-> Seq Scan on pg_class (cost=0.00..44.02 rows=602 width=72)
(4 rows)
3、按表达式排序
postgres=# explain select relpages,reltuples,relname from pg_class order by reltuples+relpages limit 1;
QUERY PLAN
------------------------------------------------------------------------
Limit (cost=50.04..50.04 rows=1 width=80)
-> Sort (cost=50.04..51.55 rows=602 width=80)
Sort Key: ((reltuples + (relpages)::double precision))
-> Seq Scan on pg_class (cost=0.00..47.03 rows=602 width=80)
5.9timestamp + numeric
PostgreSQL支持时间戳与interval类型进行加减。日期支持与整型做加减。
为了兼容Oracle(时间戳与数字加减),我们可以复写操作符来实现时间戳与数字的加减。
复写操作符
1.自定义几个函数,用于时间戳与数字的加减。
postgres=# create or replace function timestamp_add_num(timestamp, float8) returns timestamp as $$
select $1 + ($2||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
postgres=# create or replace function timestamptz_add_num(timestamptz, float8) returns timestamptz as $$
select $1 + ($2||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
postgres=# create or replace function num_add_timestamp(float8, timestamp) returns timestamp as $$
select $2 + ($1||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
postgres=# create or replace function num_add_timestamptz(float8, timestamptz) returns timestamptz as $$
select $2 + ($1||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
postgres=# create or replace function timestamp_min_num(timestamp, float8) returns timestamp as $$
select $1 - ($2||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
postgres=# create or replace function timestamptz_min_num(timestamptz, float8) returns timestamptz as $$
select $1 - ($2||' day')::interval;
$$ language sql strict immutable; CREATE FUNCTION
2.复写操作符
postgres=# create operator + (procedure = timestamp_add_num, leftarg=timestamp, rightarg=float8);
CREATE OPERATOR
postgres=# create operator + (procedure = timestamptz_add_num, leftarg=timestamptz, rightarg=float8);
CREATE OPERATOR
postgres=# create operator + (procedure = num_add_timestamp, leftarg=float8, rightarg=timestamp);
CREATE OPERATOR
postgres=# create operator + (procedure = num_add_timestamptz, leftarg=float8, rightarg=timestamptz);
CREATE OPERATOR
postgres=# create operator - (procedure = timestamp_min_num, leftarg=timestamp, rightarg=float8);
CREATE OPERATOR
postgres=# create operator - (procedure = timestamptz_min_num, leftarg=timestamptz, rightarg=float8);
CREATE OPERATOR
3.验证测试
postgres=# select now()+1;
column
-------------------------------
2017-10-25 20:03:39.256659+08
(1 row)
postgres=# select now()+1.1;
?column?
-------------------------------
2017-10-25 22:27:40.925673+08
(1 row)
postgres=# select now()-1.1;
1?column?
-------------------------------
2017-10-23 18:35:04.419078+08
(1 row)
postgres=# select 1.1+now();
?column?
-------------------------------
2017-10-25 23:23:08.842953+08
(1 row)
postgres=# select 1.1+now()::timestamp;
?column?
----------------------------
2017-10-25 23:23:13.318669
或者orafce包
5.10系统列(关键字、保留字)的处理
当我们建表时,不能使用冲突的列名,否则会报错:
postgres=# create table a(ctid int);
错误:42701: 字段名 "ctid" 与系统字段名冲突
LOCATION:CheckAttributeNamesTypes, heap.c:439
当Oracle用户要迁移到PG,遇到这样的问题怎么办呢?让用户改程序好像不太现实。
解决办法
创建影子表(将冲突字段重命名)
postgres=# create table tbl_shadow(n_ctid int, n_xmin int, n_max int, n_oid int);
CREATE TABLE
创建视图(作为业务程序中用于交互的表名),可以采用冲突字段,解决了兼容性问题。
postgres=# create view tbl1 as select n_ctid as ctid, n_xmin as xmin, n_max
as xmax, n_oid as oid from tbl_shadow ;
CREATE VIEW
对视图进行增删改查,会自动转换为对表的增删改查。
postgres=# insert into tbl1 (ctid,xmin,xmax,oid) values (1,1,1,1);
INSERT 0 1
postgres=# select ctid from tbl1;; ctid
------
1
(1 row)
postgres=# update tbl1 set xmax=2;
UPDATE 1
postgres=# select * from tbl1; ctid | xmin | xmax | oid
------+------+------+-----
1 | 1 | 2 | 1
(1 row)
5.11rowid
PostgreSQL rowid - sequence唯一标识
create table tbl (rowid serial8 not null, c1 int, c2 int);
create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl; rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
PostgreSQL rowid - IDENTITY唯一标识(适用于PostgreSQL 10+)
create table tbl (rowid int8 GENERATED ALWAYS AS IDENTITY not null,
c1 int, c2 int); create unique index idx_tbl_1 on tbl(rowid);
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 0 1
postgres=# select * from tbl; rowid | c1 | c2
-------+----+----
1 | 1 | 2
2 | 1 | 2
3 | 1 | 2
(3 rows)
PostgreSQL rowid - oids唯一标识(oid 只有32位,记录数超过40亿的单表,不适用)
postgres=# \dT oid
List of data types
Schema | Name | Description
------------+------+-------------------------------------------
pg_catalog | oid | object identifier(oid), maximum 4 billion (1 row)
例子
postgres=# create table tbl (c1 int, c2 int) with oids; CREATE TABLE
postgres=# create unique index idx_tbl_oid on tbl(oid); CREATE INDEX
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16412 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16413 1
postgres=# insert into tbl (c1,c2) values (1,2);
INSERT 16414 1
postgres=# select oid,* from tbl; oid | c1 | c2
-------+----+----
16412 | 1 | 2
16413 | 1 | 2
16414 | 1 | 2
(3 rows)
5.12round interval
Oracle可以将interval当成一个秒为单位的数值,并允许对其值进行round。
PostgreSQL的round没有写这个,不过我们可以自定义一个兼容函数。
create or replace function round(interval, int) returns float8 as $$ select round(EXTRACT(EPOCH FROM $1)::numeric, $2)::float8;
$$ language sql strict immutable;
postgres=# select round(interval '1h 10min 1.1second',2);
round
-------- 4201.1
(1 row)
14,Nvl
SELECT COALESCE(description, short_description, '(none)')
postgres=# select coalesce(null,23,45); coalesce
----------
23
(1 row)
postgres=# select coalesce(null,null,45); coalesce
----------
45
14,WM_SYS.WM_CONCAT
Oracle行转列函数WMSYS.WM_CONCAT的使用实例 demo
select * from itlife365_course a where name= '张三';
name 课程 score
张三 数学 99
张三 语文 89
张三 英语 93
上面的场景可用WMSYS.WM_CONCAT(a.name)把二行中的[课程]字段的值用","连接起来
如:
select name, to_char(WMSYS.WM_CONCAT(a.课程)) from itlife365_course a
where name= '张三'
group by a.name;
注意:因为用WMSYS.WM_CONCAT转出的类型是clob的,所以我这用了to_char转了一下。
使用wmsys.wm_concat多列合成一列遇到问题
ORA-22813: 操作数值超出系统的限制官方文档解释是总长度超过 30k
请使用其他方法替代。
PostgreSQL不存在问题,最长可以达到1GB。
PostgreSQL使用string_agg聚合函数即可达到同样的目的:
select name, string_agg(a.课程, ',')
m itlife365_course a where name= ' 张 三 ' group by a.name;
如果用户不想改代码,可以尝试自行创建一个名为WM_CONCAT的聚合函数,例子如下:
create schema WMSYS;
create or replace function WMSYS.sf_concat(text,text) returns text as
$$ select case when $1 is not null then $1||','||$2 else $2 end;
$$ language sql called on null input;
create AGGREGATE WMSYS.wm_concat (text) (sfunc=WMSYS.sf_concat,stype=text);
5.13UUID
postgres=# create extension "uuid-ossp"; CREATE EXTENSION
postgres=# create or replace function sys_guid() returns
uuid as $$ select uuid_generate_v4();
$$ language sql strict; CREATE FUNCTION
postgres=# select sys_guid();
sys_guid
--------------------------------------
92bbbf05-a23c-41b3-95d4-8732c93d95dd (1 row)
postgres=# select sys_guid();
sys_guid
--------------------------------------
37e34cfb-46aa-44ed-9403-9e23b6c2bfc0 (1 row)
5.14pipelined
create or replace function split (
p_list varchar2, p_del varchar2 := ','
) return split_tbl pipelined is
l_idx pls_integer;
l_list varchar2(32767) := p_list; l_value varchar2(32767); begin
loop
l_idx := instr(l_list,p_del); if l_idx > 0 then
pipe row(trim(substr(l_list,1,l_idx-1))); l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(trim(l_list)); exit;
end if; end loop; return; end split;
PostgreSQL:
对于以上例子的需求,可以使用现成的PostgreSQL函数来解决:
postgres=# select regexp_split_to_table('a-b-c-d','-'); regexp_split_to_table
-----------------------
a b c d
(4 rows)
如果用户只是有返回多行的需求,则可以使用returns setof来解决。例如:
postgres=# create or replace function split (text,text) returns setof text as
$$ postgres$# select regexp_split_to_table($1,$2);
postgres$# $$ language sql strict; CREATE FUNCTION
postgres=# select split('a-b-c-d','-'); split
-------
a b c d
(4 rows)
postgres=# create or replace function rsf1(id int) returns setof int as $$ postgres$# declare
postgres$# begin
postgres$# for i in 0..abs(id) loop postgres$#
return next i; postgres$# end loop; postgres$# end;
postgres$# $$ language plpgsql strict; CREATE FUNCTION
postgres=# select rsf1(10); rsf1
------
0
1
2
3
4
5
6
7
8
9
10
(11 rows)
5.15unique
Oracle: distinct unique
ect unique col1, col2 from table1
Postgres
select distinct col1, col2 from table1
六、oracle plsql迁移plpgsql
PL/pgSQL与PL/SQL在许多方面都非常类似。它是一种块结构的、命令式的语言并且所有变量必须先被声明。赋值、循环、条件则很类似。
-
在 PostgreSQL 中,函数体必须写成字符串文本。因此你需要使用美元符引用或者转义函数体中的单引号
-
数据类型名称常常需要翻译。例如,在 Oracle 中字符串值通常被声明为类型 varchar2, 这并非 SQL 标准类型。在 PostgreSQL 中则要使用类型 varchar 或者 text 来替代。类似地, 要把类型 number 替换成 numeric,或者在适当的时候使用某种其他数字数据类型。
-
应该用模式把函数组织成不同的分组,而不是用包
-
因为没有包,所以也没有包级别的变量。可以在临时表里保存会话级别的状态。
-
带有 REVERSE 的整数 FOR 循环的工作方式不同:PL/SQL 中是从第二个数向第一个数倒数,而 PL/pgSQL 是从第一个数向第二个数倒数,因此在移植时需要交换循环边界
-
查询上的 FOR 循环(不是游标)的工作方式同样不同:目标变量必须已经被声明,而PL/SQL 总是会隐式地声明它们。但是这样做的优点是在退出循环后,变量值仍然可以访问。
-
在使用游标变量方面,存在一些记法差异
6.1函数的教程
–variadic parameters, default parameters
–RETURN QUERY, CONTINUE, FOREACH SLICE,
–GET STACKED DIAGNOSTICS, ASSERT
–USAGE clause in EXECUTE
–rich RAISE statement
–plpgsql_check, Orafce
–functions: greatest, least, format, string_agg,left, right,
\sf, \ef, \gset
PLpgSQL案例
CREATE OR REPLACE FUNCTION new_customer(name text, surname text) RETURNS int AS $$
DECLARE
uid int; BEGIN
IF NOT EXISTS(SELECT * FROM customers c WHERE c.name = new_customer.name AND c.surname = new_customer.surname)
THEN
INSERT INTO customers(name, surname) VALUES(new_customer.name, new_customer.surname RETURNING id INTO uid;
RETURN uid; ELSE
RAISE EXCEPTION "Customer exists already"; END IF;
END;
$$ LANGUAGE plpgsql STRICT;
6.2plpgsql存储过程的好处
减少网络传输应用程序解耦,数据集编写,多个程序调用更安全
6.3PLpgSQL转换PL/SQL
-
循环是相同的
FOR i IN 1 .. 10 LOOP
-
pg没有immediate
FOR r IN SELECT * FROM ... EXECUTE IMMEDIATE '....'
-
输出
dbms_output.put_line(...) RAISE NOTICE '...'
-
goto语法
PLpgSQL没有GOTO
-
支持语言
Python, Perl, Lua, Java,C
-
pg/pgsql没有的特性:没有oop特性,不同于oracle的聚合和异常处理只有局部变量,没有全局变量pg没有包,使用pg的schema代替没有collections,使用数组类型dbms包,pg可以使用(Orafce兼容包) 没有自治事务,通过dblink模拟
6.4oracle函数转pg案例
oracle的函数
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
v_version varchar2)
RETURN varchar2 IS BEGIN
IF v_version IS NULL THEN RETURN v_name;
END IF;
RETURN v_name || '/' || v_version; END;
/
show errors;
postgres函数
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURNS varchar AS $$ BEGIN
IF v_version IS NULL THEN RETURN v_name;
END IF;
RETURN v_name || '/' || v_version; END;
$$ LANGUAGE plpgsql;
Oracle 版本:
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS CURSOR referrer_keys IS
SELECT * FROM cs_referrer_keys ORDER BY try_order;
func_cmd VARCHAR(4000); BEGIN
func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2
IS BEGIN';
FOR referrer_key IN referrer_keys LOOP func_cmd := func_cmd ||
' IF v_' || referrer_key.kind
|| ' LIKE ''' || referrer_key.key_string
|| ''' THEN RETURN ''' || referrer_key.referrer_type
|| '''; END IF;'; END LOOP;
func_cmd := func_cmd || ' RETURN NULL; END;'; EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;
PostgreSQL 的版本:
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS
$func$ DECLARE
referrer_keys CURSOR IS
SELECT * FROM cs_referrer_keys ORDER BY try_order;
func_body text; func_cmd text;
BEGIN
func_body := 'BEGIN';
FOR referrer_key IN referrer_keys LOOP func_body := func_body ||
' IF v_' || referrer_key.kind
|| ' LIKE ' || quote_literal(referrer_key.key_string)
|| ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
|| '; END IF;' ; END LOOP;
func_body := func_body || ' RETURN NULL; END;'; func_cmd :=
'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
v_domain varchar, v_url varchar)
RETURNS varchar AS '
|| quote_literal(func_body)
|| ' LANGUAGE plpgsql;' ;
EXECUTE func_cmd; END;
$func$ LANGUAGE plpgsql;
Oracle 版本:
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- 这将被传回去
v_path OUT VARCHAR2, -- 这个也是
v_query OUT VARCHAR2) -- 还有这个
IS
a_pos1 INTEGER; a_pos2 INTEGER;
BEGIN
v_host := NULL; v_path := NULL; v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2); v_path := '/';
RETURN; END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2); RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;
PL/pgSQL 的可能翻译:
CREATE OR REPLACE FUNCTION cs_parse_url( v_url IN VARCHAR,
v_host OUT VARCHAR, -- 这将被传递回去
v_path OUT VARCHAR, -- 这个也是
v_query OUT VARCHAR) -- 以及这个
AS $$ DECLARE
a_pos1 INTEGER; a_pos2 INTEGER;
BEGIN
v_host := NULL; v_path := NULL; v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2); v_path := '/';
RETURN; END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2); a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2); RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2); v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;
6.5oracle包函数转plpgsql的案例
Oracle PACKAGE
CREATE PACKAGE bonus AS
PROCEDURE calc_bonus(uid int); END
CREATE PACKAGE BODY bonus AS
PROCEDURE calc_bonus(uid int) IS BEGIN
DBMS_OUTPUT.PUT_LINE('started'); END;
END bonus;
PostgreSQL SCHEMA
DROP SCHEMA IF EXISTS bonus CASCADE; CREATE SCHEMA bonus;
SET search_path TO bonus;
CREATE FUNCTION calc_bonus(uid int) RETURNS void AS $$
BEGIN
RAISE NOTICE 'started'; END;
$$ LANGUAGE plpgsql SET search_path = bonus;
6.6postgresql储存过程检验
https://github.com/okbob/plpgsql_check
passive - LOAD 'plpgsql_check' (disabled by default) active - plpgsql_check_function()
pldebugger 储存过程调试
git://git.postgresql.org/git/pldebugger.git
6.7触发器
https://www.postgresql.org/docs/10/static/triggers.html
6.8事件触发器
https://www.postgresql.org/docs/10/static/event-triggers.html
6.9规则
https://www.postgresql.org/docs/10/static/rules.html
6.10plpgsql官方教程
https://www.postgresql.org/docs/10/static/plpgsql.html
6.11迁移工具介绍
目前推荐开源ora2pg迁移工具进行迁移。迁移过程略
七、JDBC客户端
7.1版本
建议使用最新版本的客户端。
不管是C/C++,JAVA,或其他语言,都建议使用最新版本的客户端依赖。因为部署的时候,默认配置了scram-sha-256的密码加密方式。需要支持PG 10或以后版本的客户端才能正常访问。
jdbc建议使用的版本号,不低于42.2.x,maven配置参考:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.12</version>
</dependency>
7.2连接参数
客户端的连接参数,建议带上ApplicationName,方便后续排查问题
JDBC例子:
String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ApplicationName=test";
Connection conn = DriverManager.getConnection(url);
带上了ApplicationName后,使用 pg_stat_activity 去排查后端连接时会更方便。
postgres=# select datname,usename , application_name , client_addr ,query from pg_stat_activity;
datname | usename | application_name | client_addr | query
----------+---------+------------------+--------------+---------------------------------------------------------------------------------------
postgres | root | Navicat | 10.142.90.32 | select datname,usename , application_name , client_addr ,query from pg_stat_activity
postgres | root | psql | | select datname,usename , application_name , client_addr ,query from pg_stat_activity;
其他JDBC参数可以参考JDBC文档:
https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters currentSchema :指定连接默认的schema。建议配一下这个参数。不然很可能会使用到了public schema,这个schema很容易导致意想不到的问题。