如何在Postgres过程中使用IN和INOUT值调用过程
提问于 2021-07-20 11:16:45
在AWS SCT(模式转换工具)的帮助下，我已经将oracle数据库迁移到RDS AWS上的Aurora Postgresql。Oracle中的一些包以以下格式转换为postgres。
CREATE OR REPLACE PROCEDURE miptd."pkg_rpt$strptst"(i_molo text,INOUT o_cursor refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
o_cursor$ATTRIBUTES aws_oracle_data.TCursorAttributes;
BEGIN
o_cursor := NULL;
OPEN o_cursor FOR
EXECUTE CONCAT_WS('','select count(ID) From
(select DISTINCT D.ID, B.CSS_DISTRICT, B.RDA, C.TECH_TYPE, C.FORECAST_DATE, D.SITE_TYPE
FROM
ALB.VIEWSIRPT B,
ALB.VIEWWRDATA C,
ALB.', i_molo, ' D
WHERE
(D.CSR = C.CSR) AND (D.CUSTOMER = C.CUSTOMER)
AND (D.CSR = B.CSR ) AND (D.CUSTOMER = B.CUSTOMER ) )a ');
o_cursor$ATTRIBUTES := ROW (TRUE, 0, NULL, NULL);
END;
$BODY$;不知道postgres中游标的用法如何调用该过程。？通过调用过程，我在哪里可以看到我的结果值？是否可以将其转换为函数？
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-07-20 12:16:35
您应该使用函数，并且应该避免SQL注入：
CREATE function some_name(i_molo text) RETURNS bigint
LANGUAGE plpgsql AS
$$BEGIN
RETURN EXECUTE
format('SELECT count(ID)
FROM (SELECT DISTINCT D.ID,
B.CSS_DISTRICT,
B.RDA, C.TECH_TYPE,
C.FORECAST_DATE,
D.SITE_TYPE
FROM ALB.%I D
JOIN ALB.VIEWWRDATA C
ON D.CUSTOMER = C.CUSTOMER AND D.CSR = C.CSR
JOIN ALB.VIEWSIRPT B
ON D.CUSTOMER = B.CUSTOMER AND D.CSR = B.CSR
) AS a',
i_molo);
END;$$;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68453934
复制
腾讯云开发者
