CODEWITHSUNDEEP
postgresqlif-statementplpgsqlstored-functions
Jay
Jay

Reputation: 372

Postgresql syntax error while trying to create function with if statement

I am new to postgresql. So, I am trying to create a function but I am getting the error message as

ERROR: syntax error at or near "IF" LINE 7: IF areaname = NULL OR pincode = NULL THEN

I refered this link.

CREATE FUNCTION location."fn_updateInArea"(area_id integer, areaname text, pincode text)
    RETURNS boolean
    LANGUAGE 'sql'

AS $BODY$
BEGIN
IF areaname IS NULL OR pincode IS NULL THEN 
RETURNING false;
ELSE
UPDATE location.areainfo SET 
area_name=CASE WHEN (areaname) IS NULL THEN (SELECT area_name from location.areainfo WHERE id=area_id) ELSE area_name END, 
pin_code=CASE WHEN (pincode) IS NULL THEN (SELECT pin_code from location.areainfo WHERE id=area_id) ELSE pin_code END
WHERE id=area_id AND "deleteIndex"=false;
RETURNING true;
END IF;
END;
$BODY$;

ALTER FUNCTION location."fn_updateInArea"(integer, text, text)
    OWNER TO postgres;

I know it would be silly error but do help me out.

Upvotes: 1

Views: 400

Answers (2)

user330315
user330315

Reputation:

There is no IF in SQL, you have to use language plpgsql if you want that. And to return a value, you need to use return, not returning.

You also don't need a SELECT statement to access the value of a column in the row you want to update, just reference the column directly. You can simplify your CASE expressions to a simple coalesce()

CREATE FUNCTION location."fn_updateInArea"(p_area_id integer, p_areaname text, p_pincode text)
    RETURNS boolean
    LANGUAGE plpgsql --<< Here
AS $BODY$
BEGIN
  IF p_areaname IS NULL OR p_pincode IS NULL THEN 
    RETURN false; --<< use RETURN not RETURNING
  ELSE
    UPDATE location.areainfo 
      SET area_name = coalesce(p_areaname, area_name)
          pin_code = coalesce(p_pincode, pin_code)
    WHERE id=area_id 
    AND "deleteIndex"=false;

    RETURN true;
  END IF;
END;
$BODY$;

I would also strongly recommend to stop using quoted identifiers they are much more trouble in the long run than they are worth it.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311163

returning is used to declare the return type of a function. In order to actually return value, you should use return:

IF areaname IS NULL OR pincode IS NULL THEN 
    RETURN false; -- Here!
ELSE
    -- etc.

Upvotes: 0

Related Questions