Returning multiple values from UPDATE query in PostgreSQL

Question

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?

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.

Is there a magical solution to my problem?

Erwin Brandstetter · Accepted Answer · 2017-07-21 22:39:40Z

6

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:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

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 (~~"user"~~), the algorithm seems to depend on being executed in exact 24h intervals, which is error-prone.

edited Jul 21, 2017 at 22:39

answered Jul 21, 2015 at 23:59

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shvalb Over a year ago

WOW!! I'm more than impressed! thank you so much for this detailed answer - I learned new useful stuff from you!

Shvalb Over a year ago

Is there a "+=" operator in plpgsql?

Erwin Brandstetter Over a year ago

@Shvalb: No, there isn't. Must be x := x + 1.

Rizwan Patel Over a year ago

any comment by @ErwinBrandstetter is goldmine in postgres ecosystem

Gary - Stand with Ukraine · Accepted Answer · 2015-07-21 13:32:24Z

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.

Update docs

answered Jul 21, 2015 at 13:32

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

7 Comments

Shvalb Over a year ago

Can I use "CASE, WHEN , END" for setting the value?

Shvalb Over a year ago

Can I do something like: RETURNING id, CASE WHEN x THEN yELSE z INTO v_user_id, v_login_at; is this possible?

Shvalb Over a year ago

Yes, it's working BUT there is an issue: What I really interested to do is accessing the value of 'last_login_at' before it gets updated by the query then do a calculation and return the results using the OUT parameter. Is there a way to access the value of 'last_login_at' without making an explicit SELECT?

Gary - Stand with Ukraine Over a year ago

Post what you've got in a fiddle at sqlfiddle.com that'll make it easier for people to see what you're doing.

Shvalb Over a year ago

Is it possible to add a function to sqlfiddle?

|

Collectives™ on Stack Overflow

Returning multiple values from UPDATE query in PostgreSQL

EDIT

2 Answers 2

4 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related