前言：本文将介绍如何将Sybase数据库中的动态SQL迁移到PostgreSQL（PG），并利用PG的动态SQL功能实现这一过程。

背景与挑战

HA原Sybase数据库部署了一个中心数据库和26个医院数据库，中心数据库的主控存储过程需要动态构建并执行调用各医院数据库的SQL。迁移到PG时，我们需要找到一种方法来模拟这种动态调用。

Sybase存储过程示例

在Sybase中，子存储过程的代码如下：

Create procedure subProc()

AS Select * FROM patient

中心数据库的主控存储过程代码如下：

Create procedure masterProc(@hospital_id int)

AS 

Declare @sql varchar(255)

Begin

-- 构建动态SQL

   Select @sql = @hospital_id +’..subProc()’

   Exec  @sql

End

PostgreSQL动态SQL实现

首先，我们需要重构子存储过程，使其通过游标返回结果集：

Create procedure qmh.subProc(INOUT cs refcursor)

LANGUAGE PLpgSQL

AS $$

begin

open cs for Select * FROM patient;

end;

$$

然后，重构主控存储过程，通过PG动态SQL调用子存储过程：

Create OR REPLACE procedure masterProc(hospital_id varchar, INOUT cs refcursor)

LANGUAGE PLpgSQL

AS $$

Declare sql varchar(255);

Begin

   sql = 'Call '|| hospital_id || '.subProc($1)';

   EXECUTE SQL USING cs;

END $$

编写测试脚本来验证我们的存储过程：

DO $$

DECLARE  cs refcursor;

BEGIN

CALL masterProc('qmh', cs);

raise notice 'cs=%', cs;

END;

$$

运行后发现返回的游标cs是NULL，这表明PG的动态SQL的参数设置USING子句只负责传值，不负责返回。为了解决这个问题，我们使用INTO子句：

Create OR REPLACE procedure masterProc(hospital_id varchar, INOUT cs refcursor)

LANGUAGE PLpgSQL

AS $$

Declare sql varchar(255);

Begin

   sql = 'Call '|| hospital_id || '.subProc($1)'; 

   EXECUTE SQL USING cs INTO cs; -- 这里增加 INTO cs子句，其它保持不变。

END $$

再次执行测试脚本，这次cs有了数据，完美！

结论

本文介绍了如何使用PostgreSQL的动态SQL功能来迁移和实现Sybase数据库中的存储过程。通过这种方式，我们可以保持数据库逻辑的一致性，同时利用PG的强大功能。动态SQL在PostgreSQL中非常灵活和强大，可以在多种场景下发挥作用。希望这篇文章能够帮助您在类似的数据库迁移项目中取得成功。