0dwT8Wag

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:
4WITH select_user AS
5(SELECT
6users.id
7FROM
8users
9WHERE
10useremail = users.email),
11
12-- Second, update the user (if user exists):
13update_user AS
14(UPDATE
15users
16SET
17authentication_code = GEN_RANDOM_UUID(),
18authentication_date = current_timestamp,
19facebook_id = facebookid
20WHERE EXISTS (SELECT * FROM select_user)
21AND
22useremail = users.email
23RETURNING
24users.authentication_code,
25users.id,
26users.key,
27users.stripe_id),
28
29-- Third, insert the user (if user does not exist):
30insert_user AS
31(INSERT INTO
32users (authentication_code, authentication_date, email, key, name, facebook_id)
33SELECT
34GEN_RANDOM_UUID(),
35current_timestamp,
36useremail,
37GEN_RANDOM_UUID(),
38COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
39facebookid
40WHERE NOT EXISTS (SELECT * FROM select_user)
41RETURNING
42users.authentication_code,
43users.id,
44users.key,
45users.stripe_id)
46
47-- Finally, select the authentication code, ID, key and Stripe ID:
48SELECT
49*
50FROM
51update_user
52UNION ALL
53SELECT
54*
55FROM
56insert_user' LANGUAGE "sql" COST 100 ROWS 1
57VOLATILE
58CALLED ON NULL INPUT
59SECURITY INVOKER
60	
61CREATE TABLE users
62(
63    id SERIAL PRIMARY KEY,
64    email TEXT NOT NULL,
65    column_that_we_will_drop TEXT
66) ;
67
68-- Function that uses the previous table, and that has a CTE
69CREATE OR REPLACE FUNCTION __post_users
70    (_useremail text) 
71RETURNS integer AS
72$$
73-- Need a CTE to produce the error. A 'constant' one suffices.
74WITH something_even_if_useless(a) AS
75(
76    VALUES (1)
77)
78UPDATE
79    users
80SET
81    id = id
82WHERE 
83    -- The CTE needs to be referenced, if the next
84    -- condition were not in place, the problem is not reproduced
85    EXISTS (SELECT * FROM something_even_if_useless)
86    AND email = _useremail
87RETURNING
88    id
89$$
90LANGUAGE "sql" ;
91	
92SELECT * FROM __post_users('a@b.com');
93	
94ALTER TABLE users 
95    DROP COLUMN column_that_we_will_drop ;
96	
97SELECT * FROM __post_users('a@b.com');
98	
99ERROR: table row type and query-specified row type do not match
100SQL state: 42804
101Detail: Query provides a value for a dropped column at ordinal position 3.
102Context: SQL function "__post_users" statement 1
103    SELECT * FROM __post_users('a@b.com');
104	
105ERROR:  column users.authentication_code does not exist
106LINE 24: users.authentication_code,