在PostgreSQL中返回更新查询中的多个值

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.

我刚开始编写DB函数，在执行更新查询时，我需要返回'last_login_at'的值作为OUT参数。

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?

有可能将x多次返回到y中吗?我们可以用CASE语句将x返回到y中吗?

EDIT

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.

唯一的问题是在返回last_login_at时已经被更新，所以CASE总是返回TRUE。

Is there a magical solution to my problem?

我的问题有神奇的解决办法吗?

2 个解决方案

#1

Is there a magical solution to my problem?

我的问题有神奇的解决办法吗?

Actually, there is: Join to another instance of the "user" table in the FROM clause:

实际上，有:连接到FROM子句中的“user”表的另一个实例:

   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.

现在，表别名u表示表的更新后状态，而u1表示查询开始时的快照。

Detailed explanation:

详细解释:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version
仅使用SQL - PostgreSQL版本返回预更新列值

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:

您可以在return子句中使用任何表达式，包括CASE语句。这里有一个更简单，更便宜的方法:

CASE WHEN date_part('day', age(current_timestamp, last_login_at)) = 0
THEN true ELSE false END

Step 1:

步骤1:

CASE WHEN last_login_at < now() + interval '1 day'
THEN true ELSE false END

Step 2:

步骤2:

(last_login_at < now() + interval '1 day')

Just use the boolean result. If last_login_at is NULL, you get NULL.

只使用布尔结果。如果last_login_at为NULL，就会得到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 ( ~~"user"~~ ), the algorithm seems to depend on being executed in exact 24h intervals, which is error-prone.

Asides:至于查询的其余部分:表达式可以简化，循环是可疑的，您永远不应该使用保留字作为标识符，即使双引号使之成为可能(“user”)，算法似乎依赖于以精确的24小时间隔执行，这很容易出错。

#2

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.

return子句遵循了SELECT字段列表的大部分规则，因此您可以添加任意多的列。

Update docs

更新文档

#1