如何通過 EXECUTE 在動態 SQL 中使用函式參數？

我在 PostgreSQL 9.5 中編寫了一個 PL/pgSQL 函式。它編譯得很好，但是當我從 pgAdmin3 呼叫它時，它給了我一個錯誤。使用傳入函式的參數替換列的動態查詢似乎不起作用。

以下是我的功能：

CREATE OR REPLACE FUNCTION insertRecordsForNotification(username text, state     text, district text, organizationId text, bloodGroup text, status text,   approveRejectStatus text, emailSubject text, emailBody text, notificationStatus text) RETURNS boolean AS $$
DECLARE
id int;
r moyadev.user%rowtype;
_where text :=
 concat_ws(' AND '
   , CASE WHEN state IS NOT NULL THEN 'state = $2' END
   , CASE WHEN district IS NOT NULL THEN 'district = $3' END
   , CASE WHEN bloodGroup IS NOT NULL THEN 'bloodGroup = $5' END
   , CASE WHEN status IS NOT NULL THEN 'status = $6' END
   , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $7' END);   
_sql text := 'INSERT INTO moyadev.notification_email_details (id,     youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,  sent_date, last_updated_by, last_updated) SELECT uuid_generate_v4(), id, email,  $8, $9, $10, null, null,$1, now() FROM moyadev.youth_enrollment';

BEGIN

SELECT * into r FROM moyadev.user u where u.user_key=$1;


if (r.level='DISTRICT') then
_where := _where || ' AND ' || 'district=r.district' || ' AND ' ||      'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
elseif (r.level='STATE') then
_where := _where || ' AND ' || 'state=r.state' || ' AND ' ||   'fk_id=r.fk_id';
elseif (r.level='NATIONAL') then
_where := _where || ' AND ' || 'fk_id=r.fk_id';
elseif (r.level='UNIT') then
_ where := _where || ' AND ' || 'district=r.district' || ' AND ' ||    'state=r.state' || ' AND ' || 'fk_id=r.fk_id';
end if;

IF _where <> '' THEN
_sql := _sql || ' WHERE ' || _where;
EXECUTE format(_sql);
END IF;

raise notice 'sql: %', _sql;

RETURN 'TRUE';

END; 
$$ LANGUAGE PLPGSQL;

它編譯得很好，但是當我使用以下命令呼叫它時會出現以下錯誤：

select insertRecordsForNotification('nssnationalappr@mailinator.com',null,null,null,null,'ACTIVE','APPROVED','test email','test email','PENDING');

ERROR: there is no parameter $8
SQL state: 42P02
Context: PL/pgSQL function insertrecordsfornotification(text,text,text,text,text,text,text,text,text,text) line 39 at EXECUTE

如何正確使用參數值？

你混淆了幾件事。要將值傳遞給EXECUTE，請使用該USING子句。你不需要format()這裡。

CREATE OR REPLACE FUNCTION insert_records_for_notification(
       _username text
     , _state text
     , _district text
     , _bloodgroup text
     , _status text
     , _approverejectstatus text
     , _emailsubject text
     , _emailbody text
     , _notificationstatus text)
 RETURNS boolean
 LANGUAGE plpgsql AS
$func$
DECLARE
  r      moyadev.user%rowtype;
  _where text;
  _sql   text :=
    'INSERT INTO moyadev.notification_email_details (id, youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,sent_date, last_updated_by, last_updated)
     SELECT uuid_generate_v4(), id, email, $7, $8, $9, null, null,$1, now()
     FROM   moyadev.youth_enrollment';
BEGIN
  SELECT * INTO r FROM moyadev.user u WHERE u.user_key = _username;

  _where := concat_ws(' AND '
      , CASE WHEN state               IS NOT NULL THEN 'state = $2'                  END
      , CASE WHEN district            IS NOT NULL THEN 'district = $3'               END
      , CASE WHEN bloodGroup          IS NOT NULL THEN 'bloodgroup = $4'             END
      , CASE WHEN status              IS NOT NULL THEN 'status = $5'                 END
      , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $6' END
      , CASE r.level
           WHEN 'DISTRICT' THEN 'district = $10 AND state = $11 AND fk_id = $12'
           WHEN 'UNIT'     THEN 'district = $10 AND state = $11 AND fk_id = $12'
           WHEN 'STATE'    THEN 'state = $11 AND fk_id = $12'
           WHEN 'NATIONAL' THEN 'fk_id = $12'
        END);

  IF _where <> '' THEN
     _sql := _sql || ' WHERE ' || _where;

     EXECUTE _sql
     USING   $1, $2, $3, $4, $5, $6, $7, $8, $9, r.district, r.state, r.fk_id;
  END IF;

  RAISE NOTICE 'sql: %', _sql;

  RETURN true;  -- boolean!
END
$func$;

要點

不要將參數值連接到 SQL 字元串中。非常乏味、緩慢、容易出錯並且對 SQL 注入開放。而是將值傳遞給EXECUTEwithUSING子句。看：

• Postgres 函式中的 SQL 注入與準備好的查詢

我刪除了未使用的變數id int;和未使用的參數organizationId text。相應地調整了序號引用 ( $n)。

不要將$n內部的符號EXECUTE（參考USING子句中的項目）與$n函式體中的符號（參考函式參數）混淆！有關的：

• 將數組從一個 plpgsql 函式傳遞到另一個

簡化了連接WHERE子句的邏輯。存在極端情況錯誤：如果初始分配導致空字元串，您將從AND- 語法錯誤開始。

採用避免命名衝突的命名約定。參數名稱在函式中的所有語句中都可見（但不在內部EXECUTE！）。不要使用與列名衝突的變數名。一個常見的約定是在參數和變數名稱前加上_.

我的建議是避免在 Postgres 中混合大小寫標識符，尤其是在使用動態 SQL 時。

也可以看看：

• 測試具有不同參數的函式中的空值

引用自：https://dba.stackexchange.com/questions/159424