Feature: Procedures or Functions

[mathe42]

Is your feature request related to a problem?

I have different use cases for this. Basicly make a callable list of surrealQL instructions with arguments etc.

This would allow us to reduce our API-Size to a minimum.

Describe the solution

My proposal contains a few extensions to statements

• CALL
• DEFINE PROCEDURE (with RETURN keyword)

Define a procedure to get a person

DEFINE PROCEDURE get_person ($firstname, $lastname, $birthdate) {
  LET $person = SELECT * FROM person WHERE firstname = $firstname AND lastname = $lastname AND birthdate = $birthdate;
  
  IF (COUNT($person)==1) THEN
     RETURN 1;
  ELSE
    RETURN CREATE person {
       firstname: $firstname,
       lastname: $lastname,
       birthdate: $birthdate
    }
  END
}

and call it by

LET $myPerson = CALL get_person('Sebastian', 'Krüger', '2022-09-21')

Alternative methods

The specifc syntax doesn't matter the above is only an example that I thought about.

There are some things things to look at:

1. types,
2. permissions (we might want to allow to run SQL of procedures even if for that statement the user has no permission but for the call of the procedure)
3. Transaction I would expect that the Procedure is automaticly wraped in a transaction if there is an error at any statement it should fail and commit nothing to db.

(#225 might be silightly releated)

SurrealDB version

surreal 1.0.0-beta.7 for windows on x86_64

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[MatthewAry]

If this feature gets added, it would be nice to be able to call a procedure in an event. DEFINE EVENT

[kearfy]

+1 for this. I currently created a kinda hacky table structure with a table that drops all info and some events that allow me to reuse certain queries but this would make it miles easier

[kearfy]

Would like to comment on this: I don't think it would make sense to have a CALL statement to execute the procedures, as this will force you to use subqueries to invoke them, eventually exhausting the limits faster. I'd personally say that prefixing them like this: prodedure::name_of_procedure(arg1, arg2, ...) to invoke them, makes more sense.

Per your example: LET $myPerson = procedure::get_person('Sebastian', 'Krüger', '2022-09-21')

[Nezteb]

This is the one major feature I was waiting to be added before giving SurrealDB a try. You are a wizard @tobiemh, thanks!