时间戳

首页 > 解决方案 > Postgresql 中的反射器

postgresql - Postgresql 中的反射器

问题描述

早上好，

我有一个关于如何查看在 Output refcursor 中获取的数据的快速问题

我正在执行以下块，它已成功执行，但我想查看在输出 RefCursor 中获取的数据，顺便说一下我使用 PGADMIN 4 工具连接到 Postgres 服务器，

BEGIN;
SELECT prod_package_list ('G6028|G6026|G6025|G6024|G6022|G6021|G6020', NULL);
FETCH ALL IN vref_cur; 
COMMIT;

我在网上找到，根据他们的说法，我应该遵循以下格式，

BEGIN;
SELECT test_prod_package_list ('G6028|G6026|G6025|G6024|G6022|G6021|G6020', NULL, 'vref_cur');
FETCH ALL IN "vref_cur"; 
COMMIT;

但是上面的执行块会抛出一个错误函数 prod_package_list(unknown, unknown, unknown) 不存在，

功能如下，

CREATE OR REPLACE FUNCTION ssp2_pcat.prod_package_list(
    prodlist text,
    billcodelist text,
    OUT vref_cur refcursor)
    RETURNS refcursor
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$

BEGIN
    vref_cur := 'vref_cur';

    IF prodlist IS NOT NULL THEN
        OPEN vref_cur FOR
        SELECT DISTINCT
            b.package_id, a.product_id, a.pdct_ctlg_id, a.product_name, a.billing_system, a.billing_code, a.fulfill_system, a.fulfill_code, a.speed_code, a.product_type, a.product_type_value, a.return_auth_applies, a.return_auth_term, a.nrc_waiver, a.nrc_waiver_product_id, a.linked_nrc_product_id, a.national_east_west, a.lata_list, a.state_list, a.product_info_url, a.isp_prov_flags, a.product_coefficient, a.last_updated, a.product_keywords, a.combo_product_list, a.additional_info, a.usoc_info, a.min_billing_days, a.data_nrf_product_id, a.video_nrf_product_id, a.account_line_level, a.web_desc, a.orderguicd, a.prod_startdate, a.prod_enddate, b.pdct_ctlg_id, b.package_name, b.billing_code, b.category_id, b.standalone_cpe, b.standalone_truckroll, b.btn_billed, b.emp_discount, b.package_type, b.last_updated, b.pkg_detail_descr, b.grandfathered AS pkg_grandfathered,
            CASE CONCAT_WS('', a.grandfathered, c.pkg_prod_grandfathered)
                WHEN 'YY' THEN 'Y'
                WHEN 'YN' THEN 'Y'
                WHEN 'NY' THEN 'Y'
                ELSE 'N'
            END AS grandfathered
            FROM ssp2_pcat.products AS a, ssp2_pcat.packages AS b, ssp2_pcat.package_products AS c
            WHERE strpos(prodlist, CONCAT_WS('', '|', a.product_id, '|')) > 0 
            AND a.product_id = c.product_id AND c.package_id = b.package_id 
            AND c.start_date <= LOCALTIMESTAMP AND c.end_date >= LOCALTIMESTAMP
            ORDER BY 1;

    ELSIF billcodelist IS NOT NULL THEN
        OPEN vref_cur FOR
        SELECT DISTINCT
            b.package_id, a.product_id, a.pdct_ctlg_id, a.product_name, a.billing_system, a.billing_code, a.fulfill_system, a.fulfill_code, a.speed_code, a.product_type, a.product_type_value, a.return_auth_applies, a.return_auth_term, a.nrc_waiver, a.nrc_waiver_product_id, a.linked_nrc_product_id, a.national_east_west, a.lata_list, a.state_list, a.product_info_url, a.isp_prov_flags, a.product_coefficient, a.last_updated, a.product_keywords, a.combo_product_list, a.additional_info, a.usoc_info, a.min_billing_days, a.data_nrf_product_id, a.video_nrf_product_id, a.account_line_level, a.web_desc, a.orderguicd, a.prod_startdate, a.prod_enddate, b.pdct_ctlg_id, b.package_name, b.billing_code, b.category_id, b.standalone_cpe, b.standalone_truckroll, b.btn_billed, b.emp_discount, b.package_type, b.last_updated, b.pkg_detail_descr, b.grandfathered AS pkg_grandfathered,
            CASE CONCAT_WS('', a.grandfathered, c.pkg_prod_grandfathered)
                WHEN 'YY' THEN 'Y'
                WHEN 'YN' THEN 'Y'
                WHEN 'NY' THEN 'Y'
                ELSE 'N'
            END AS grandfathered
            FROM ssp2_pcat.products AS a, ssp2_pcat.packages AS b, ssp2_pcat.package_products AS c
            WHERE strpos(billcodelist, CONCAT_WS('', '|', a.billing_code, '|')) > 0 
            AND a.product_id = c.product_id AND c.package_id = b.package_id 
            AND c.start_date <= LOCALTIMESTAMP AND c.end_date >= LOCALTIMESTAMP
            ORDER BY 1;

    ELSE
        RAISE USING hint = -20001, message = 'Product List and Billing Code Lists are empty.', detail = 'User-defined exception';
    END IF;

END;

$BODY$;

ALTER FUNCTION ssp2_pcat.prod_package_list(text, text)
    OWNER TO ssp2_pcat;

请指教，

标签： postgresqlplpgsql

解决方案

使用IN参数传递游标名称。不要忘记RETURN vref_cursor。工作示例：

CREATE FUNCTION my_func(arg1 text, arg2 text, vref_cursor refcursor) 
RETURNS refcursor AS $$
BEGIN
    OPEN vref_cursor FOR SELECT generate_series(1,3);
    RETURN vref_cursor;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT my_func('first arg', 'second arg', 'vref_cursor');
FETCH ALL IN vref_cursor;
COMMIT;

推荐阅读