I'm new to writing DB functions and I need to return the value of 'last_login_at' as OUT
parameter when performing an UPDATE
query.
Here is a snippet of my function:
...
LOOP
UPDATE "user" SET
last_login_at = current_timestamp,
first_name = p_first_name,
last_name = p_last_name,
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id INTO v_user_id;
is_new := false;
// The next 'CASE' is not valid - Need to replace it with a valid one.
has_logged_in_today = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) > 1
THEN true
ELSE false
END;
IF FOUND THEN
EXIT;
END IF;
..
..
END LOOP;
Is it possible to do multiple RETURNING x INTO y
?
Can we use a CASE
statement in RETURNING x INTO y
?
I was able to get better results and now it looks like this:
...
LOOP
UPDATE "user" SET
login_consecutive_days = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) > 1
THEN 0
ELSE login_consecutive_days + date_part('day', age(current_timestamp, last_login_at))
END,
login_max_consecutive_days = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) = 1
AND (login_consecutive_days+1 > login_max_consecutive_days)
THEN login_consecutive_days+1
ELSE login_max_consecutive_days
END,
last_login_at = current_timestamp,
num_sessions = num_sessions + 1,
last_update_source = 'L',
first_name = p_first_name,
last_name = p_last_name,
additional_data = p_additional_data
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id,
CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) = 0
THEN true
ELSE false
END
INTO v_user_id, is_first_login_today;
is_new := false;
IF FOUND THEN
EXIT;
END IF;
...
The only problem with this is that at the point of RETURNING
the last_login_at
has already been updated so CASE
always returns TRUE
.
Is there a magical solution to my problem?
Is there a magical solution to my problem?
Actually, there is: Join to another instance of the "user"
table in the FROM
clause:
UPDATE "user" u
SET login_consecutive_days = ... -- unqualified column name
FROM "user" u1
WHERE u.ext_user_id = p_ext_user_id
AND u.platform_id = p_platform_id
AND u.id = u1.id -- must be unique not null (like the PK)
RETURNING u.id, (u1.last_login_at < now() + interval '1 day')
INTO v_user_id, is_first_login_today;
is_new := false;
EXIT WHEN FOUND;
Now, the table alias u
refers to the post-UPDATE
state of the table, but u1
refers to a snapshot at the start of the query.
Detailed explanation:
Table-qualify all column references to be unambiguous, which is never a bad idea, but after the self-join it's required.
The manual about the short syntax EXIT WHEN FOUND
.
You can use any expression in the RETURNING
clause, including CASE
statements. There just happens to be a simpler, cheaper way for this:
CASE WHEN date_part('day', age(current_timestamp, last_login_at)) = 0
THEN true ELSE false END
Step 1:
CASE WHEN last_login_at < now() + interval '1 day'
THEN true ELSE false END
Step 2:
(last_login_at < now() + interval '1 day')
Just use the boolean
result. If last_login_at
is NULL
, you get NULL
.
Asides:
As for the rest of the query: expressions can be simplified, the LOOP
is suspicious, you should never use reserved words as identifiers, even though double-quoting makes it possible (), the algorithm seems to depend on being executed in exact 24h intervals, which is error-prone."user"
You can return multiple columns with a syntax of:
UPDATE "user" SET
last_login_at = current_timestamp,
first_name = p_first_name,
last_name = p_last_name,
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id, last_login_at
INTO v_user_id, v_login_at;
The returning clause follows most of the rules of a SELECT field list so you can add as many columns as you need.
Update docs
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With