5

I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the parameters passed in function is not working.

Below is my function:

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;

It compiles well but gives below error when I call it using below command:

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

How to use parameter values properly?

10

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.

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 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$  LANGUAGE plpgsql;

Major points

  • Do not concatenate parameter values into SQL strings. Very tedious, slow, error-prone and open to SQL injection. Instead pass values to EXECUTE with the USING clause. Related:

  • I removed unused variable id int; and the unused parameter organizationId text. Adapted ordinal reference ($n) accordingly.

  • Do not confuse the $n notation inside EXECUTE (refer to items in the USING clause) with $n notation in the function body (refer to function parameters)! Related:

  • Simplified your logic to concatenate the WHERE clause. There were corner case errors: if the initial assignment resulted in empty string, you would start with AND - a syntax error.

  • Adopt a naming convention that avoids naming conflicts. Parameter names are visible in all statements in the function (but not inside EXECUTE!). Don't use variable names that conflict with column names. A common convention is to prepend parameter and variable names with _.

  • My advise is to avoid mixed-case identifiers in Postgres, especially when using dynamic SQL.

Related answer on SO:

  • Yes Sir, It worked after removing just one typo error. Thank you so much ! I am going to edit and accept it! – Pushpendra Pal Jan 2 '17 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.