CREATE
OR REPLACE FUNCTION "public"."fs_gm_adv_id" ( "school_id" int4 ) RETURNS "pg_catalog"."text" AS $BODY$ DECLARE --定义初始变量
v_ret_val TEXT;
fdcl_id record;
base_id1 INTEGER [];
distributor_id1 INTEGER;
adv_id1 INTEGER [];
BEGIN
v_ret_val := '';
FOR fdcl_id IN SELECT -- 遍历 获取id, line_id
"id",
line_id
FROM
fs_distribution_contract_line
WHERE
line_state = 'service'
AND "id" IN ( SELECT fs_distribution_contract_line_id FROM fs_distribution_contract_line_fs_distribution_routes_rel WHERE fs_distribution_routes_id IN ( SELECT fs_distribution_routes_id FROM fs_department_management_fs_distribution_routes_rel WHERE fs_department_management_id = school_id ) )
ORDER BY
"id"
LOOP
RAISE NOTICE'Counter1: % %',
fdcl_id."id",
fdcl_id.line_id; -- 初次打印变量
-- 循环中查询子查询 并赋值给base_id1 通过sql中的 INTO
SELECT ARRAY
(
SELECT
base_id
FROM
fs_food_category_line
WHERE
cate_id IN ( SELECT fs_food_category_id FROM fs_distribution_contract_line_fs_food_category_rel WHERE fs_distribution_contract_line_id = fdcl_id."id" )
AND active = TRUE
AND depart_education_or_school_id IN ( SELECT parent_id FROM fs_department_management WHERE "id" = school_id UNION SELECT school_id )
GROUP BY
base_id
ORDER BY
base_id
) INTO base_id1;
SELECT
distributor_id
FROM
fs_distribution_contract
WHERE
"id" = fdcl_id.line_id INTO distributor_id1;
RAISE NOTICE'Counter2: % %',
base_id1,
distributor_id1; --第二次打印变量
-- 判断base_id1数组中有值时才执行IF中的操作
IF
array_length( base_id1, 1 ) > 0 THEN
SELECT ARRAY
( SELECT "id" FROM fs_goods_management_advance WHERE active = TRUE AND distributor_id = distributor_id1 AND base_product_name = ANY ( base_id1 ) ) INTO adv_id1;
RAISE NOTICE'Counter3: %',
adv_id1;
v_ret_val := v_ret_val || ',' || array_to_string( adv_id1, ',' );
END IF; --结束IF
END LOOP; --结束循环
v_ret_val := SUBSTRING ( v_ret_val, 2 ); --截取掉字符串中的第一个,(逗号)
RETURN v_ret_val;
END $BODY$ LANGUAGE plpgsql VOLATILE COST 100
-- unnest 数组转record列
SELECT unnest(string_to_array(fs_gm_adv_id, ',')) from fs_gm_adv_id(3);
SELECT "id" from fs_goods_management_advance WHERE "id" in (SELECT unnest(string_to_array(fs_gm_adv_id, ','))::int from fs_gm_adv_id(2));
-- ::int 将字符串格式的类型转为int
SELECT "unnest"::int from (SELECT unnest(string_to_array(fs_gm_adv_id, ',')) from fs_gm_adv_id(3)) a;
|
