Postgres函式int陣列引數錯誤？

我寫了一個 postgres 函式，它接受一個整數陣列作為引數

create or replace function f_get_profiles(
            exp_array jsonb, 
            lng_array int[] ??
        ) 

我將此函式稱為：

SELECT * FROM 
f_get_profiles(
  '[{"role": 1, "experience": 2 }, {"role": 2, "experience": 3}]',
   array[1,2,3,4]::int[]
);

在我想將它用作常規整數陣列的函式中，我嘗試了兩種方式

format(' AND languages && %s', lng_array)

和

format(' AND languages && array[%s]', lng_array)

兩者都給我錯誤：

錯誤 ：

ERROR:  syntax error at or near "{"
LINE 3: ....role == 2 && @.experience >= 3)' AND languages && {1,2,3,4}

和

ERROR:  syntax error at or near "{"
LINE 3: ...= 2 && @.experience >= 3)' AND languages && array[{1,2,3,4}]

分別....

有人可以幫忙嗎？我在這里錯過了什么>？

全功能

create or replace function f_get_profiles(
            exp_array jsonb, 
            lng_array int[], 
            _limit int=10, 
            _offset int=0
        ) 
    returns setof profiles 
    language plpgsql parallel SAFE stable strict as 
    $func$
        declare final_sql text;
        begin
            select into final_sql 
                E'SELECT * FROM profiles \nWHERE expertise @? ' ||
                string_agg(
                    quote_nullable(format('$[*] ? (@.role == %s && @.experience >= %s)', f->'role', f->'experience')) || '',
                    E'\nOR expertise @? '
                ) || format(' AND languages && %s', lng_array)
                  || E'\nLIMIT ' || _limit  || E'\nOFFSET ' || _offset
            from jsonb_array_elements(exp_array) as f;
            if final_sql is null then
                raise exception 'SQL statement is NULL. Should not occur!';
            else 
                return query execute final_sql;
            end if;
        end
    $func$;

uj5u.com熱心網友回復：

盡管可能，但不要將 value 引數連接到查詢字串中。將它們作為value傳遞。
走出去，你的功能可能看起來像這樣：

CREATE OR REPLACE FUNCTION f_get_profiles(exp_array jsonb, lng_array int[])
   RETURNS SETOF tbl
   LANGUAGE sql AS 
$func$
SELECT *
FROM   tbl t
WHERE  ...  -- more predicates
AND    t.languages && lng_array
$func$;

如果您確實需要在 PL/pgSQL 函式中使用動態 SQL，請使用USING子句傳遞值：

CREATE OR REPLACE FUNCTION f_get_profiles(exp_array jsonb, lng_array int[])
   RETURNS SETOF tbl
   LANGUAGE plpgsql AS 
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   $q$
   SELECT *
   FROM   tbl t
   WHERE  ...  -- more predicates
   AND    t.languages && $1
   $q$)
   USING  lng_array;
END;
$func$

例子：

• 不能在 plpgsql 函式中使用帶有 EXECUTE 命令的變數
• 在觸發器函式中插入動態表名

應用于您的功能

CREATE OR REPLACE FUNCTION f_get_profiles(
            _exp_array jsonb,
            _lng_array int[],
            _limit int = 10,
            _offset int = 0)
  RETURNS void -- SETOF profiles
  LANGUAGE plpgsql PARALLEL SAFE STABLE STRICT AS
$func$
DECLARE
   final_sql text;
BEGIN
   SELECT INTO final_sql
          format(
$q$SELECT * FROM profiles 
WHERE  (%s)
AND    languages && $1  -- ??
LIMIT  3
OFFSET 4$q$   
               , string_agg(format($s$jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', %L)$s$, f), E'OR ')
               , _limit  
               , _offset)
   FROM jsonb_array_elements(_exp_array) AS f;
   
   IF final_sql IS NULL THEN
       RAISE EXCEPTION 'SQL statement is NULL. Should not occur!';
   ELSE
       RETURN QUERY EXECUTE final_sql USING _lng_array;    -- ??
   END IF;
END
$func$;

執行以下形式的陳述句：

SELECT * FROM profiles 
WHERE  (jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', '{"role": 1, "experience": 2}')
     OR jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', '{"role": 2, "experience": 3}'))
AND    languages && $1
LIMIT  3
OFFSET 4

我@?用jsonb_path_exists(). 這樣，我們可以直接將帶引數的 JSON 物件傳遞給函式。

在此程序中，我在 OR'ed 謂詞周圍添加了缺失的括號。記住：AND系結之前OR。

替代語法

或者我們堅持使用@?，但使用單個 JSON 路徑運算式：

CREATE OR REPLACE FUNCTION pg_temp.f_get_profiles(
            _exp_array jsonb,
            _lng_array int[],
            _limit int = 10,
            _offset int = 0)
  RETURNS void -- SETOF profiles
  LANGUAGE plpgsql PARALLEL SAFE STABLE STRICT AS
$func$
DECLARE
   final_sql text;
BEGIN
   SELECT INTO final_sql
          format(
$q$SELECT * FROM profiles 
WHERE  expertise @? '$[*] ? (%s)'
AND    languages && $1   -- ??
LIMIT  3
OFFSET 4$q$   
               , string_agg(format('@.role == %s && @.experience >= %s', (f->'role')::int, (f->'experience')::int), ' || ')
               , _limit  
               , _offset)
   FROM jsonb_array_elements(_exp_array) AS f;

   IF final_sql IS NULL THEN
       RAISE EXCEPTION 'SQL statement is NULL. Should not occur!';
   ELSE
      RETURN QUERY EXECUTE final_sql USING _lng_array;    -- ??
   END IF;
END
$func$;

執行以下形式的陳述句：

SELECT * FROM profiles 
WHERE  expertise @? '$[*] ? (@.role == 1 && @.experience >= 2 || @.role == 2 && @.experience >= 3)'
AND    languages && $1
LIMIT  3
OFFSET 4

我在這里滑入的轉換為整數 (f->'role')::int, (f->'experience')::int)的唯一目的是防御其他可能的 SQL 注入。如果您可以排除 SQLi，您可以剝離演員表。

標籤：

上一篇：使用CTE插入表

下一篇：為什么AVG查詢在Postgres中一個月后不起作用？