在单个参数中传递多个值

问题描述

我想通过在单个参数上传递多个值来调用函数，如下所示:

I want to call a function by passing multiple values on single parameter, like this:

SELECT * FROM jobTitle('270,378');

这是我的职责.

CREATE OR REPLACE FUNCTION test(int)
RETURNS TABLE (job_id int, job_reference int, job_job_title text
                                            , job_status text) AS
$$
BEGIN
RETURN QUERY
select jobs.id,jobs.reference, jobs.job_title,
       ltrim(substring(jobs.status,3,char_length(jobs.status))) as status
FROM jobs ,company c
WHERE jobs."DeleteFlag" = '0'
and c.id= jobs.id and c.DeleteFlag = '0' and c.active = '1' 
and (jobs.id = $1 or -1 = $1)
order by jobs.job_title;
END;
$$ LANGUAGE plpgsql;

有人可以提供语法帮助吗?甚至提供示例代码?

Can someone help with the syntax? Or even provide sample code?

推荐答案

类似于已提供@mu ， VARIADIC 是您的朋友.一个更重要的细节:

Like @mu already provided, VARIADIC is your friend. One more important detail:

您还可以使用具有数组类型的VARIADIC参数直接调用函数.在函数调用中添加关键字VARIADIC:

You can also call a function using a VARIADIC parameter with an array type directly. Add the key word VARIADIC in the function call:

SELECT * FROM  f_test(VARIADIC '{1,2,3}'::int[]);

等效于:

SELECT * FROM  f_test(1,2,3);

其他建议

• 在Postgres 9.1+中 负的> 更快，更简单，可从字符串中修剪前导字符:

  Other advice

  • In Postgres 9.1+ right() with a negative length is faster and simpler to trim leading characters from a string:

    right(j.status, -2)
    

    等效于:

    substring(j.status, 3, char_length(jobs.status))
    

  • 查询中同时具有j."DeleteFlag"和j.DeleteFlag(不带双引号). 这可能是错误的.

  • You have j."DeleteFlag" as well as j.DeleteFlag (without double quotes) in your query. This is probably incorrect.

    "DeleteFlag" = '0'表示另一个问题.与其他RDBMS不同，Postgres完全且正确地支持 布尔 数据类型. 如果标志包含布尔数据(是/否/可能为NULL)，请使用boolean类型.像text 这样的字符类型是不合适的.

    "DeleteFlag" = '0' indicates another problem. Unlike other RDBMS, Postgres fully and properly supports the boolean data type. If the flag holds boolean data (Yes / No / possibly NULL) use the boolean type. A character type like text would be inappropriate.

    您在这里不需要plpgsql.您可以为此使用更简单的SQL函数:

    You don't need plpgsql here. You can use a simpler SQL function for this:

    所有这些放在一起，您的函数可能如下所示:

    All of this put together, your function could look like this:

    CREATE OR REPLACE FUNCTION f_test(VARIADIC int[])
      RETURNS TABLE (id int, reference int, job_title text, status text) AS
    $func$
    
    SELECT j.id, j.reference, j.job_title
          ,ltrim(right(j.status, -2)) AS status
    FROM   company c
    JOIN   job     j USING (id)
    WHERE  c.active
    AND    NOT c.delete_flag
    AND    NOT j.delete_flag
    AND   (j.id = ANY($1) OR '{-1}'::int[] = $1)
    ORDER  BY j.job_title
    
    $func$ LANGUAGE sql;
    

    SQLfiddle演示

    这篇关于在单个参数中传递多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！