PervasiveSQL - Inserting/Updating record inside and If Else statement

Question

G'day. I'm trying to create a basic function which checks for the number of records matching a certain criteria. If there are none then it performs an insert and returns returns 0, otherwise it updates the existing record and returns 1. I'm getting a syntax error at the location of the Insert and Update statements. Here is my SQL script (FYI I'm new to pervasive and if anyone has a better way to perform an update/insert then I'm all ears/eyes):

CREATE FUNCTION "InsertUpdateWebData"(:KeyType CHAR(1), :KeyValue CHAR(50), 
:WebData CHAR(100), :WhiteSpace LONGVARCHAR, :Spare CHAR(97)) RETURNS INTEGER 
AS
BEGIN
  DECLARE :RecordCount INTEGER;
  SET     :RecordCount = (SELECT COUNT(*) FROM SYS_WebData WHERE WBD_KeyType 
           = :KeyType and WBD_KeyValue = :KeyValue);

  IF :RecordCount = 0 THEN
  BEGIN
    INSERT INTO SYS_WebData(WBD_KeyType, WBD_KeyValue, WBD_Data, 
    WBD_WhiteSpace, WBD_Spare) VALUES (:KeyType, :KeyValue, :WebData, 
    :WhiteSpace, :Spare);
    RETURN  0;
  END
  ELSE
  BEGIN
    UPDATE SYS_WebData SET WBD_Data = :WebData, WBD_WhiteSpace = :WhiteSpace, 
    WBD_Spare = :Spare WHERE WBD_KeyType = :KeyType AND WBD_KeyValue = 
    :KeyValue;
    RETURN 1;      
  END
  END IF
END 

What would be the correct syntax to do this?

Answer 1

From the Pervasive documentation:

Restrictions

You cannot use the CREATE DATABASE or the DROP DATABASE statement in a user-defined function. The table actions CREATE, ALTER, UPDATE, DELETE, and INSERT are not permitted within a user-defined function.

You should be able to change it from CREATE FUNCTION to CREATE PROCEDURE and have it work. I did that and it created the procedure.