yUPPCwEb

1CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '
2
3-- First, select the user:
4(SELECT
5users.id
6FROM
7users
8WHERE
9useremail = users.email),
10
11-- Second, update the user (if user exists):
12update_user AS
13(UPDATE
14users
15SET
16authentication_code = GEN_RANDOM_UUID(),
17authentication_date = current_timestamp,
18facebook_id = facebookid
19WHERE EXISTS (SELECT * FROM select_user)
20AND
21useremail = users.email
22RETURNING
23users.authentication_code,
24users.id,
25users.key,
26users.stripe_id),
27
28-- Third, insert the user (if user does not exist):
29insert_user AS
30(INSERT INTO
31users (authentication_code, authentication_date, email, key, name, facebook_id)
32SELECT
33GEN_RANDOM_UUID(),
34current_timestamp,
35useremail,
36GEN_RANDOM_UUID(),
37COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
38facebookid
39WHERE NOT EXISTS (SELECT * FROM select_user)
40RETURNING
41users.authentication_code,
42users.id,
43users.key,
44users.stripe_id)
45
46-- Finally, select the authentication code, ID and key:
47SELECT
48*
49FROM
50update_user
51UNION ALL
52SELECT
53*
54FROM
55insert_user' LANGUAGE "sql" COST 100 ROWS 1
56VOLATILE
57CALLED ON NULL INPUT
58SECURITY INVOKER