From bad to worse

Overloading functions

Last chapter, we used the fight() function for some characters, but most only have {} for the strength property. That’s why the Innkeeper defeated Dracula, which is obviously not what would really happen.

Jonathan Harker is just a human but is still quite strong. We’ll give him a strength of 5. We’ll treat that as the maximum strength for a human, except Renfield who is a bit unique. Every other human should have a strength between 1 and 5. EdgeDB has a random function called std::rand() that gives a float64 in between 0.0 and 1.0. There is another function called round() that rounds numbers, so we’ll use that too, and finally cast it to an <int16>. Our input looks like this:

SELECT <int16>round(random() * 5);

So now we’ll use this to update our Person types and give them all a random strength.

WITH random_5 := (SELECT <int16>round(random() * 5))
 # WITH isn't necessary - just making the query prettier

UPDATE Person
FILTER NOT EXISTS .strength
SET {
  strength := random_5
};

And we’ll make sure Count Dracula gets 20 strength, because he’s Dracula:

UPDATE Vampire
FILTER .name = 'Count Dracula'
SET {
  strength := 20
};

Now let’s SELECT Person.strength; and see if it works:

{3, 3, 3, 2, 3, 2, 2, 2, 3, 3, 3, 3, 4, 1, 5, 10, 4, 4, 20, 4, 4, 4, 4}

Looks like it worked.

So now let’s overload the fight() function. Right now it only works for one Person vs. another Person, but in the book all the characters get together to try to defeat Dracula. We’ll need to overload the function so that more than one character can work together to fight. There are a lot of ways to do it, but we’ll choose a simple one:

function fight(names: str, one: int16, two: Person) -> str
  using (
    SELECT names ++ ' win!' IF one > two.strength ELSE two.name ++ ' wins!'
  );

Note that overloading only works if the function signature is different. Here are the two signatures we have now for comparison:

fight(one: Person, two: Person) -> str
fight(names: str, one: int16, two: Person) -> str

If we tried to overload it with an input of (Person, Person), it wouldn’t work because it’s the same. That’s because EdgeDB uses the input we give it to know which form of the function to use.

So now it’s the same function name, but we enter the names of the people together, their strength together, and then the Person they are fighting.

Now Jonathan and Renfield are going to try to fight Dracula together. Good luck!

WITH
  jon_and_ren_strength := <int16>(
    SELECT sum(
      (SELECT NPC FILTER .name IN {'Jonathan Harker', 'Renfield'}).strength
    )
  ),
  dracula := (SELECT Person FILTER .name = 'Count Dracula'),

SELECT fight('Jon and Ren', jon_and_ren_strength, dracula);

So did they…

{'Count Dracula wins!'}

No, they didn’t win. How about four people?

WITH
  four_people_strength := <int16>(
    SELECT sum(
      (
        SELECT NPC
        FILTER .name IN {'Jonathan Harker', 'Renfield', 'Arthur Holmwood', 'The innkeeper'}
      ).strength
    )
  ),
  dracula := (SELECT Person FILTER .name = 'Count Dracula'),

  SELECT fight('The four people', four_people_strength, dracula);

Much better:

{'The four people win!'}

So that’s how function overloading works - you can create functions with the same name as long as the signature is different.

You see overloading in a lot of existing functions, such as sum which takes in all numeric types and returns the sum. std::to_datetime has even more interesting overloading with all sorts of inputs to create a datetime.

fight() was pretty fun to make, but that sort of function is better done on the gaming side. So let’s make a function that we might actually use. Since EdgeQL is a query language, the most useful functions are usually ones that make queries shorter.

Here is a simple one that tells us if a Person type has visited a Place or not:

function visited(person: str, city: str) -> bool
  using (
    WITH person := (SELECT Person FILTER .name = person LIMIT 1),
    SELECT city IN person.places_visited.name
  );

Now our queries are much faster:

edgedb> 

SELECT visited('Mina Murray', 'London');

{true}

edgedb> 

SELECT visited('Mina Murray', 'Bistritz');

{false}

Thanks to the function, even more complicated queries are still quite readable:

SELECT(
  'Did Mina visit Bistritz? ' ++ <str>visited('Mina Murray', 'Bistritz'),
  'What about Jonathan and Romania? ' ++ <str>visited('Jonathan Harker', 'Romania')
);

This prints {('Did Mina visit Bistritz? false', 'What about Jonathan and Romania? true')}.

The documentation for creating functions is here. You can see that you can create them with SDL or DDL but there is not much difference between the two. In fact, they are so similar that the only difference is the word CREATE that DDL needs. In other words, just add CREATE to make a function without needing to do an explicit migration. For example, here’s a function that just says hi:

function say_hi() -> str
  using ('hi');

If you want to create it right now, just do this:

CREATE FUNCTION say_hi() -> str
  USING ('hi');

(or with lowercase letters, it doesn’t matter)

You’ll see more or less the same thing when you ask to DESCRIBE FUNCTION say_hi:

{'CREATE FUNCTION default::say_hi() ->  std::str USING (\'hi\');'}

Deleting (dropping) functions

You can delete a function with the DROP keyword and the function signature. You only have to specify the input though, because the input is all that EdgeDB looks at when identifying a function. So in the case of our two fight() functions:

fight(one: Person, two: Person) -> str
fight(names: str, one: int16, two: Person) -> str

You would delete them with DROP fight(one: Person, two: Person) and DROP fight(names: str, one: int16, two: Person). The -> str part isn’t needed.

More about Cartesian products - the coalescing operator

Now let’s learn more about Cartesian products in EdgeDB. You might be surprised to see that even a single {} input always results in an output of {}, but this is the way that Cartesian products work. Remember, a {} has a length of 0 and anything multiplied by 0 is also 0. For example, let’s try to add the names of places that start with b and those that start with f.

WITH b_places := (SELECT Place FILTER Place.name ILIKE 'b%'),
     f_places := (SELECT Place FILTER Place.name ILIKE 'f%'),
SELECT b_places.name ++ ' ' ++ f_places.name;

The output is….maybe unexpected if you didn’t read the previous paragraph.

{}

!! It’s an empty set. But a search for places that start with b gives us {'Buda-Pesth', 'Bistritz'}. Let’s see if the same works when we concatenate with ++ as well.

SELECT {'Buda-Pesth', 'Bistritz'} ++ {};

So that should give a {}. The output is…

error: operator '++' cannot be applied to operands of type 'std::str' and 'anytype'
  ┌─ query:1:8
  │
1 │ SELECT {'Buda-Pesth', 'Bistritz'} ++ {};
  │        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Consider using an explicit type cast or a conversion function.

Another surprise! This is an important point though: EdgeDB requires a cast for an empty set, because it won’t try to guess at what type it is. There’s no way to guess the type of an empty set if all we give it is {}, so EdgeDB won’t try. You can probably guess that the same is true for array constructors too, so SELECT []; returns an error: QueryError: expression returns value of indeterminate type.

Okay, one more time, this time making sure that the {} empty set is of type str:

edgedb> 

SELECT {'Buda-Pesth', 'Bistritz'} ++ <str>{};

{}

Good, so we have manually confirmed that using {} with another set always returns {}. But what if we want to:

• Concatenate the two strings if they exist, and

• Return what we have if one is an empty set?

In other words, how to add {'Buda-Peth', 'Bistritz'} to another set and return the original {'Buda-Peth', 'Bistritz'} if the second is empty?

To do that we can use the so-called coalescing operator, which is written ??. The explanation for the operator is nice and simple:

Evaluate to A for non-empty A, otherwise evaluate to B.

So if the item on the left is not empty it will return that, and otherwise it will return the one on the right.

Here is a quick example:

edgedb> 

SELECT <str>{} ?? 'Count Dracula is now in Whitby';

Because we used ?? instead of ++, the result is {'Count Dracula is now in Whitby'} and not {}.

So let’s get back to our original query, this time with the coalescing operator:

WITH b_places := (SELECT Place FILTER .name ILIKE 'b%'),
     f_places := (SELECT Place FILTER .name ILIKE 'f%'),
SELECT b_places.name ++ ' ' ++ f_places.name
  IF EXISTS b_places.name AND EXISTS f_places.name
  ELSE b_places.name ?? f_places.name;

This returns:

{'Buda-Pesth', 'Bistritz'}

That’s better.

But now back to Cartesian products. Remember, when we add or concatenate sets we are working with every item in each set separately. So if we change the query to search for places that start with b (Buda-Pesth and Bistritz) and m (Munich):

WITH b_places := (SELECT Place FILTER .name ILIKE 'b%'),
     m_places := (SELECT Place FILTER .name ILIKE 'm%'),
SELECT b_places.name ++ ' ' ++ m_places.name
  IF EXISTS b_places.name AND EXISTS m_places.name
  ELSE b_places.name ?? m_places.name;

Then we’ll get this result:

{'Buda-Pesth Munich', 'Bistritz Munich'}

instead of something like ‘Buda-Peth, Bistritz, Munich’.

Let’s experiment some more while introducing two new functions, called array_agg and array_join. Here’s what they do:

• array_agg, turns sets into arrays (it ‘aggregates’ them).

• array_join turns arrays into a single string. So let’s give that a try:

WITH b_places := (SELECT Place FILTER .name ILIKE 'b%'),
     m_places := (SELECT Place FILTER .name ILIKE 'm%'),
SELECT array_join(array_agg(b_places.name), ', ') ++ ', ' ++
  array_join(array_agg(m_places.name), ', ')
  IF EXISTS b_places.name AND EXISTS m_places.name
  ELSE b_places.name ?? m_places.name;

This looks not too bad: the output is {'Buda-Pesth, Bistritz, Munich'}. But there’s a small problem:

• if both sets are not empty we get a single string with commas,

• otherwise we get a set of strings.

So that’s not very robust. Plus the query is kind of hard to read now.

The best way is actually the easiest: just UNION the sets.

WITH b_places := (SELECT Place FILTER .name ILIKE 'b%'),
     m_places := (SELECT Place FILTER Place.name ILIKE 'm%'),
     both_places := b_places UNION m_places,
SELECT both_places.name;

Finally! The output is {'Buda-Pesth', 'Bistritz', 'Munich'}

Now with this more robust query we can use it on anything and don’t need to worry about getting {} if we choose a letter like x. Let’s look at every place that contains k or e:

WITH has_k := (SELECT Place FILTER .name ILIKE '%k%'),
     has_e := (SELECT Place FILTER .name ILIKE '%e%'),
     has_both := has_k UNION has_e,
SELECT has_both.name;

This gives us the result:

{'Slovakia', 'Buda-Pesth', 'Castle Dracula'}

Similarly, you can use ?= instead of = and ?!= instead of != when doing comparisons if you think one side might be an empty set. So then you can write a query like this:

WITH cities1 := {'Slovakia', 'Buda-Pesth', 'Castle Dracula'},
     cities2 := <str>{}, # Don't forget to cast to <str>
SELECT cities1 ?= cities2;

and get the output

{
  false,
  false,
  false
}

instead of {} for the whole thing. Also, two empty sets are treated as equal if you use ?=. So this query:

SELECT Vampire.lover.name ?= Crewman.name;

will return {true}. (Because Dracula has no lover and the Crewmen have no names so both sides return empty sets of type str.)

Here is all our code so far up to Chapter 12.

1. Consider these two functions. Will EdgeDB accept the second one?

   First function:

   Copy

   function gives_number(input: int64) -> int64
     using(input);
   

   Second function:

   Copy

   function gives_number(input: int64) -> int32
     using(<int32>input);
   

   Show answer

2. How about these two functions? Will EdgeDB accept the second one?

   First function:

   Copy

   function make64(input: int16) -> int64
     using(input);
   

   Second function:

   Copy

   function make64(input: int32) -> int64
     using(input);
   

   Show answer

3. Will SELECT {} ?? {3, 4} ?? {5, 6}; work?

   Show answer

4. Will SELECT <int64>{} ?? <int64>{} ?? {1, 2} work?

   Show answer

5. Trying to make a single string of everyone’s name with SELECT array_join(array_agg(Person.name)); isn’t working. What’s the problem?

   Show answer

Up next: One of the men gives his blood to try to save Lucy. Will it be enough?