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?
Best Answer
You are confusing a couple of things. To pass values to
EXECUTE
, use theUSING
clause. You don't needformat()
here.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 theUSING
clause. Related:I removed unused variable
and the unused parameterid int;
. Adapted ordinal reference (organizationId text
$n
) accordingly.Do not confuse the
$n
notation insideEXECUTE
(refer to items in theUSING
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 withAND
- 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: