Possibility of creating edgeql function with conditional insert

[Fogapod]

I've been trying to make a function that creates users with no success today.
Here's what I've got so far:

        function create_user(nickname: str, email: str, pass: bytes) -> optional User
        {
            volatility := 'VOLATILE';
            using EdgeQL $$
                WITH
                    MODULE DEFAULT
                    conflicting := (
                        SELECT User {
                            created_at,
                        }
                        FILTER .nickname = nickname OR .email = email
                        LIMIT 2
                    ),
                    deleted := (
                        FOR x IN conflicting
                        UNION (
                                DELETE
                                x
                            IF
                                NOT .email_verified
                                AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                            ELSE
                                <User>{}
                        )
                    )
                SELECT (
                    (INSERT User {
                        nickname := nickname,
                        email := email,
                        password := password,
                    })
                IF
                    len(deleted) = len(conflicting)
                ELSE
                    <User>{}
                )
            $$;
        };

Explanation:

• I search existing users by nickname and email (both should be unique) and store them in conflicting
• I delete users from conflicting if they did not confirm email within 5 days and store deleted users in deleted
• If conflicting == deleted (none were conflicting or all conflicts resolved), create new user and return it
• Otherwise return empty set

The code I have so far does not work for multiple reasons

• Conditional inserts are not possible yet: SELECT Object IF EXISTS Object ELSE INSERT  #684
• FOR x IN conflicting raises unexpected conflicting for some reason
• I'm not sure how to return empty set of users, see my previous post in discussions - this is a confirmed bug

Is this function possible with current state of edegql and edgedb or I should wait for EdgePL?

[elprans]

FOR x IN conflicting raises unexpected conflicting for some reason

You must use a set literal in FOR, so the correct syntax is:

FOR x IN {conflicting}

[Fogapod]

This did not work, I still get the exact same error.
I created a quick and dirty gist for testing that can be pasted directly into edgedb cli: https://gist.github.com/Fogapod/4c4b31ea344b135059b291d772d056ec

[elprans]

There are a couple more issues with that function: missing comma after MODULE DEFAULT and DEFAULT should be lowercase.

Putting DML statements inside conditionals is not supported (aside from a specific case that is being worked on in #684), so you cannot do DELETE foo IF condition ELSE ..., instead you should put the deletion condition into the FILTER clause of the DELETE statement, like so:

DELETE
    User
FILTER
    User = conflicting
    AND NOT .email_verified
    AND (
        datetime_of_transaction() - .created_at) 
        > to_duration(hours := 24 * 5)
    )

Also, the function parameters should be referred to as just nickname, not $nickname.

Here's a rewritten version of create_user:

        function create_user(nickname: str, email: str, pass: bytes) -> optional User
        {
            volatility := 'VOLATILE';
            using EdgeQL $$
                WITH
                    MODULE default,
                    conflicting := (
                        SELECT User {
                            created_at,
                        }
                        FILTER .nickname = nickname OR .email = email
                        LIMIT 2
                    ),
                    deleted := (
                        DELETE
                        User
                        FILTER
                            User = conflicting
                            AND NOT .email_verified
                            AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                    )
                SELECT (
                    (INSERT User {
                        nickname := nickname,
                        email := email,
                        password := password,
                    })
                IF
                    len(deleted) = len(conflicting)
                ELSE
                    <User>{}
            )
            $$;
        };

This leaves conditional INSERT, and in this case it's not possible to express the delete-and-then-insert logic in a single query. This is actually a Postgres limitation.

What is possible (with #684 in mind) is an UPDATE-or-INSERT to reuse the existing User object:

        function create_user(nickname: str, email: str, pass: bytes) -> optional User
        {
            volatility := 'VOLATILE';
            using (
                WITH
                    MODULE default
                SELECT (
                    (
                        UPDATE User
                        FILTER 
                            (.nickname = nickname OR .email = email)
                            AND NOT .email_verified
                            AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                        SET {
                             created_at := datetime_of_transaction(),
                             # ... reset all other properties
                        }
                    )
                    ??  # no users were updated, no conflict so INSERT
                    (
                        INSERT User {
                            nickname := nickname,
                            email := email,
                            password := password,
                        }
                    )
                )
            );
        };

While the above would work, I don't think it's a good solution, because you're essentially be reusing other user's object and may inadvertently expose private data. So treat it as an example of logic for more benign cases.

[Fogapod]

The code is indeed is very messy, sorry for that.

DELETE
    User
FILTER
    User = conflicting

Shouldn't this be User IN conflicting instead?

The UPDATE option is interesting, but I don't want to deal with all leftover links, duplicate default values etc.
EdgePL is probably the way. Until then, application logic.

[elprans]

Shouldn't this be User IN conflicting instead?

Yes, IN is cleaner, although both would work (via slightly different reduction to a final boolean value).

[Fogapod]

After a few recent bugfixes I was able to test new approach using this #684 (comment) hack, but I still have 1 more issue: nothing is ever returned from function.

        function create_user(nickname: str, email: str, password: bytes) -> optional User
        {
            volatility := 'VOLATILE';
            using EdgeQL $$
                WITH
                    MODULE default,
                    conflicting := (
                        SELECT User {
                            created_at,
                        }
                        FILTER .nickname = nickname OR .email = email
                        LIMIT 2
                    ),
                    deleted := (
                        DELETE
                        User
                        FILTER
                            User IN conflicting
                            AND NOT .email_verified
                            AND (datetime_of_transaction() - .created_at) > to_duration(hours := 24 * 5)
                    )
                SELECT (
                    FOR _ IN {(
                        SELECT true
                        FILTER count(conflicting) = count(deleted)
                    )}
                    UNION (
                        INSERT User {
                            nickname := nickname,
                            email := email,
                            password := password,
                        }
                    )
                )
            $$
        }

Did I make some obvious mistake here?
Function successfully creates user, deletes old users, but it never returns created user.
I tried removing SELECT around FOR loop, but it did not work.
I also tried setting FOR filter to always be true, removing optional from return type, using set of, but nothing helps.

My tests in REPL successfully return user:

SELECT (
FOR _ IN {(select true filter true)}
UNION (INSERT User{nickname := 'abcde', email := 'a@b.c', password := b''})
);

I created a gist with simplified schema: https://gist.github.com/Fogapod/0ad0763be7e90e6ab46cb49325e54eab

[elprans]

Oh, this is a very interesting case. The reason why the function doesn't "return" is because the query from which you are calling it doesn't yet see the result of INSERT, only subsequent statements would see it. Here's a demonstration of this behavior in PostgreSQL:

=# CREATE TABLE person (id serial, name text);
CREATE TABLE

=# CREATE OR REPLACE FUNCTION new_person(name text) RETURNS int AS $$ 
$#     INSERT INTO person (name) VALUES (name) RETURNING id; 
$# $$ VOLATILE LANGUAGE SQL;
CREATE FUNCTION

# SELECT * FROM person WHERE id = new_person('Alice');
 id │ name
────┼──────
(0 rows)

=# SELECT * FROM person WHERE name = 'Alice';
   id   │ name
────────┼───────
 200006 │ Alice
(1 row)

We might be able to solve this by inlining the entire function body into a calling query, but that is a very large can of worms to consider for now.

Bottom line: avoid functions with data-modifying statements for now.