时间戳

首页 > 解决方案 > plpgsql 函数 concat optionnal args 返回错误

sql - plpgsql 函数 concat optionnal args 返回错误

问题描述

我想连接由 AND 分隔的可选参数，并返回那些不为空的连接参数的 varchar。

CREATE OR REPLACE FUNCTION shop_apply_search_filters(price_min INTEGER DEFAULT NULL, price_max INTEGER DEFAULT NULL, ecom_id  INTEGER DEFAULT NULL, 
                                                        cat_1 VARCHAR DEFAULT NULL, cat_2 VARCHAR DEFAULT NULL)
  RETURNS VARCHAR AS
$$
DECLARE final_filters VARCHAR(500);
BEGIN
    IF price_min IS NOT NULL THEN
        SELECT CONCAT('price<',price_min) AS n_price_min;
        final_filters := n_price_min;
    END IF;

    IF price_max IS NOT NULL THEN
        SELECT CONCAT('price>',price_max) AS n_price_max;
        final_filters := CONCAT(final_filters,' AND ', n_price_max);
    END IF;

    IF ecom_id IS NOT NULL THEN
        SELECT CONCAT('ecom_id=',ecom_id) AS n_ecom_id;
        final_filters := CONCAT(final_filters,' AND ', n_ecom_id);
    END IF;

    IF cat_1 IS NOT NULL THEN
        SELECT CONCAT('category_1:',cat_1) AS n_cat_1;
        final_filters := CONCAT(final_filters,' AND ', n_cat_1);
    END IF;

    IF cat_2 IS NOT NULL THEN
        SELECT CONCAT('category_2:',cat_2) AS n_cat_2;
        final_filters := CONCAT(final_filters,' AND ', n_cat_2);
    END IF;

    RETURN final_filters;
END;
$$
LANGUAGE PLPGSQL;

SELECT shop_apply_search_filters(10) 的输出将是一个类似于“价格 > 10”的字符串。调用函数时是否可以传递参数名称？为了能够区分 price_min 和 price_max 如果只有其中一个通过。是否可以将非空参数附加到列表，然后使用 AND 加入列表元素？

你会怎么做？

编辑

我在 CONCAT() 时删除了 SELECT() 以避免错误。但我有一个新的：

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function shop_apply_search_filters(integer,integer,integer,character varying,character varying) line 5 at SQL statement
SQL state: 42601

编辑 我试过：

create or replace function shop_apply_search_filters(
    price_min integer default null, 
    price_max integer default null, 
    ecom_id  integer default null, 
    cat_1 text default null, 
    cat_2 text default null)
returns text as
$$
    select concat_ws(
        ' and ',
        'price < '      || price_min,
        'price > '      || price_max,
        'ecom_id = '    || ecom_id,
        'category_1 = ' || cat_1,
        'category_2 = ' || cat_2
    );
$$
language sql;

它适用于所有传递的参数：

SELECT shop_apply_search_filters(10,10,10,'cat_1','cat_2')

当并非所有参数都通过时如何处理？

SELECT shop_apply_search_filters(10,10,'cat_1','cat_2')

ERROR:  invalid input syntax for integer: "cat_1"
LINE 1: SELECT shop_apply_search_filters(10,10,'cat_1','cat_2')
                                               ^
SQL state: 22P02
Character: 40

SELECT shop_apply_search_filters(10,10)
ERROR:  function shop_apply_search_filters(integer, integer) is not unique
LINE 1: SELECT shop_apply_search_filters(10,10)
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
SQL state: 42725
Character: 8

标签： sqlstringpostgresqlstored-proceduresplpgsql

解决方案

为什么不直接使用concat_ws()来一次连接整个字符串呢？

concat_ws(
    ' and ',
    'price < '      || price_min,
    'price > '      || price_max,
    'ecom_id = '    || ecom_id,
    'category_1 = ' || cat_1,
    'category_2 = ' || cat_2
)

然后，您可以将整个过程简化为：

create or replace function shop_apply_search_filters(
    price_min integer default null, 
    price_max integer default null, 
    ecom_id  integer default null, 
    cat_1 text default null, 
    cat_2 text default null)
returns text as
$$
    select concat_ws(
        ' and ',
        'price < '      || price_min,
        'price > '      || price_max,
        'ecom_id = '    || ecom_id,
        'category_1 = ' || cat_1,
        'category_2 = ' || cat_2
    );
$$
language sql;

推荐阅读