Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE users (
  user_id       integer
, user_name     varchar(250)
, last_activity timestamptz
, created       timestamptz
);

INSERT INTO users VALUES
  (1, 'foo', '2020-07-07 12:22:33+0', '2020-07-07 11:22:33+0')
, (2, 'bar', '2020-08-08 23:33:44+0', '2020-08-08 22:33:44+0')
;

CREATE TABLE



INSERT 0 2


CREATE OR REPLACE FUNCTION get_user_by_username(_username text, _online bool DEFAULT false)
  RETURNS TABLE (
    user_id int
  , user_name varchar
  , last_activity timestamptz
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u.user_id
              , u.user_name
              , u.last_activity;
   ELSE
      RETURN QUERY
      SELECT u.user_id
           , u.user_name
           , u.last_activity
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

CREATE FUNCTION


SELECT * FROM get_user_by_username('foo', true);


user_id
user_name
last_activity


1
foo
2024-02-08 06:26:09.271421+00



SELECT 1


SELECT * FROM get_user_by_username('bar');  -- defaults to false


user_id
user_name
last_activity


2
bar
2020-08-09 00:33:44+01



SELECT 1


TABLE users ORDER BY user_id;


user_id
user_name
last_activity
created


1
foo
2024-02-08 06:26:09.271421+00
2020-07-07 12:22:33+01

2
bar
2020-08-09 00:33:44+01
2020-08-08 23:33:44+01



SELECT 2


CREATE OR REPLACE FUNCTION get_user_by_username2(_username text, _online bool DEFAULT false)
  RETURNS SETOF users
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp
      WHERE  u.user_name = _username
      RETURNING u.*;
   ELSE
      RETURN QUERY
      SELECT *
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

CREATE FUNCTION


SELECT * FROM get_user_by_username2('foo', true);


user_id
user_name
last_activity
created


1
foo
2024-02-08 06:26:09.277719+00
2020-07-07 12:22:33+01



SELECT 1


SELECT * FROM get_user_by_username2('bar', false);


user_id
user_name
last_activity
created


2
bar
2020-08-09 00:33:44+01
2020-08-08 23:33:44+01



SELECT 1


TABLE users ORDER BY user_id;


user_id
user_name
last_activity
created


1
foo
2024-02-08 06:26:09.277719+00
2020-07-07 12:22:33+01

2
bar
2020-08-09 00:33:44+01
2020-08-08 23:33:44+01



SELECT 2


CREATE OR REPLACE FUNCTION get_user_by_username3(_username text, _online bool DEFAULT false)
  RETURNS TABLE (
    users_row users
  , custom_addition text
  )
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _online THEN
      RETURN QUERY
      UPDATE users u 
      SET    last_activity = current_timestamp  -- ts with time zone
      WHERE  u.user_name = _username
      RETURNING u  -- whole row
              , u.user_name || u.user_id;
   ELSE
      RETURN QUERY
      SELECT u, u.user_name || u.user_id
      FROM   users u
      WHERE  u.user_name = _username;
   END IF;
END
$func$;

CREATE FUNCTION


SELECT (users_row).*, custom_addition FROM get_user_by_username3('foo', true);


user_id
user_name
last_activity
created
custom_addition


1
foo
2024-02-08 06:26:09.282109+00
2020-07-07 12:22:33+01
foo1



SELECT 1


SELECT (users_row).*, custom_addition FROM get_user_by_username3('foo');


user_id
user_name
last_activity
created
custom_addition


1
foo
2024-02-08 06:26:09.282109+00
2020-07-07 12:22:33+01
foo1



SELECT 1


TABLE users ORDER BY user_id;


user_id
user_name
last_activity
created


1
foo
2024-02-08 06:26:09.282109+00
2020-07-07 12:22:33+01

2
bar
2020-08-09 00:33:44+01
2020-08-08 23:33:44+01



SELECT 2

user_id	user_name	last_activity	created
1	foo	2024-02-08 06:26:09.271421+00	2020-07-07 12:22:33+01
2	bar	2020-08-09 00:33:44+01	2020-08-08 23:33:44+01

user_id	user_name	last_activity	created
1	foo	2024-02-08 06:26:09.277719+00	2020-07-07 12:22:33+01
2	bar	2020-08-09 00:33:44+01	2020-08-08 23:33:44+01

user_id	user_name	last_activity	created
1	foo	2024-02-08 06:26:09.282109+00	2020-07-07 12:22:33+01
2	bar	2020-08-09 00:33:44+01	2020-08-08 23:33:44+01