Postgresql function has syntax error at If. Why?

Question

I am new to writing functions on postgres. So I was trying to write a function that checks if a row exists. If it exists then it returns the row with a flag 'Exists', else it inserts a row and returns it with a flag 'New'.

But I am facing a simple syntax error which I am not able to find out.

ERROR: syntax error at or near "IF" LINE 11: IF EXISTS ( ^ SQL state: 42601 Character: 208

This is the sql function.

CREATE FUNCTION create_food_type (
  foodTypeName TEXT,
  foodTypeIsActive BOOLEAN,
  foodTypeCreatedBy INT,
  foodTypeModifiedBy INT
)
RETURNS TABLE (operation TEXT, result JSON)
LANGUAGE SQL
AS $$
BEGIN
  IF EXISTS (
    SELECT *
    FROM "FoodType"
    WHERE "FoodTypeName" = foodTypeName
  ) THEN
    -- Return the existing row and flag indicating row already exists
    RETURN QUERY (
      SELECT 'existing' AS operation, json_build_object(
        'FoodTypeId', "FoodTypeID",
        'FoodTypeName', "FoodTypeName",
        'FoodTypeIsActive', "FoodTypeIsActive",
        'FoodTypeCreatedDate', "FoodTypeCreatedDate",
        'FoodTypeCreatedBy', "FoodTypeCreatedBy",
        'FoodTypeModifiedDate', "FoodTypeModifiedDate",
        'FoodTypeModifiedBy', "FoodTypeModifiedBy"
      )
      FROM "FoodType"
      WHERE "FoodTypeName" = foodTypeName
      LIMIT 1
    );
  ELSE
    -- Insert a new row and return the newly inserted row and flag indicating new row added
    RETURN QUERY (
      INSERT INTO "FoodType" ("FoodTypeName", "FoodTypeIsActive", "FoodTypeCreatedDate", "FoodTypeCreatedBy", "FoodTypeModifiedDate", "FoodTypeModifiedBy")
      VALUES (foodTypeName, foodTypeIsActive, CURRENT_TIMESTAMP, foodTypeCreatedBy, CURRENT_TIMESTAMP, foodTypeModifiedBy)
      RETURNING 'new' AS operation, json_build_object(
        'FoodTypeId', "FoodTypeID",
        'FoodTypeName', "FoodTypeName",
        'FoodTypeIsActive', "FoodTypeIsActive",
        'FoodTypeCreatedDate', "FoodTypeCreatedDate",
        'FoodTypeCreatedBy', "FoodTypeCreatedBy",
        'FoodTypeModifiedDate', "FoodTypeModifiedDate",
        'FoodTypeModifiedBy', "FoodTypeModifiedBy"
      )
    );
  END IF;
END;
$$;

I have tried CASE WHEN as well.

 IF EXISTS (
    SELECT *
    FROM "FoodType"
    WHERE "FoodTypeName" = foodTypeName
  ) THEN
--Do Something
 ELSE
--Do Something
 END IF;

Even this throws the same error at 'CASE'

The function is tagged with `SQL` as the language, but the body appears to be `plpgsql`. — JohnH, May 14 '23 at 16:18
never use column names as variable names this would be a problem and get you the wrong result# — nbk, May 14 '23 at 18:10
@nbk, while good advice, the better option is to avoid unqualified identifiers in queries. This practice guards against someone adding columns that cause conflicts. This issue affects both variable and column identifiers. References to function parameters can be qualified with the function's name. References to local variables can be qualified using labels. My practice is to add the label `<>` immediately after `DECLARE` and then qualifying each variable in a query by prepending `local.`. The goal is to minimize the risk that external changes will break the code. — JohnH, May 14 '23 at 20:55

SelVazi · Accepted Answer · 2023-05-14T18:18:03.923

The body language should be plpgsql, also add suffix to your parameters for better readability :

CREATE FUNCTION create_food_type (
  p_foodTypeName TEXT,
  p_foodTypeIsActive BOOLEAN,
  p_foodTypeCreatedBy TEXT,
  p_foodTypeModifiedBy TEXT
)
RETURNS TABLE (operation TEXT, result JSON)
AS $$
DECLARE
  operation text default 'existing';
BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM FoodType
    WHERE FoodTypeName = p_foodTypeName
  ) THEN
      operation = 'new';
      INSERT INTO FoodType (FoodTypeName, FoodTypeIsActive, FoodTypeCreatedDate, FoodTypeCreatedBy, FoodTypeModifiedDate, FoodTypeModifiedBy)
      VALUES (p_foodTypeName, p_foodTypeIsActive, CURRENT_TIMESTAMP, p_foodTypeCreatedBy, CURRENT_TIMESTAMP, p_foodTypeModifiedBy);
  END IF;
  RETURN QUERY (
      SELECT operation, json_build_object(
        'FoodTypeId', FoodTypeID,
        'FoodTypeName', FoodTypeName,
        'FoodTypeIsActive', FoodTypeIsActive,
        'FoodTypeCreatedDate', FoodTypeCreatedDate,
        'FoodTypeCreatedBy', FoodTypeCreatedBy,
        'FoodTypeModifiedDate', FoodTypeModifiedDate,
        'FoodTypeModifiedBy', FoodTypeModifiedBy
      )
      FROM FoodType
      WHERE FoodTypeName = p_foodTypeName
      LIMIT 1
    );
END;
$$ LANGUAGE plpgsql;

Postgresql function has syntax error at If. Why?

1 Answers1