typeof in PostgreSQL

[xluffy]

In Python, we have type(variable) method or typeof in Javascript to check the variable type.

So I tried to implement a stored procedure. And I want to check the type of return data. I learn about pg_typeof same as the above methods.

E.g.:

CREATE OR REPLACE FUNCTION get_first_last_id(date TEXT) RETURNS INTEGER[] AS $$
DECLARE
  -- date = '2022-08'
  first_id INTEGER := (SELECT id FROM users WHERE TO_CHAR(created_at::date, 'yyyy-dd') = date ORDER BY id LIMIT 1);
  last_id INTEGER := (SELECT id FROM users WHERE TO_CHAR(created_at::date, 'yyyy-dd') = date ORDER BY id DESC LIMIT 1);
  arr INTEGER[] := ARRAY[]::INTEGER[];
BEGIN
  arr = array_append(arr, first_id);
  arr = array_append(arr, last_id);
  RETURN arr;
END;
$$
LANGUAGE plpgsql;

So I try to get data for testing.

> SELECT get_first_last_id('2023-01');
  get_first_last_id
---------------------
 {32723815,32885071}

> SELECT pg_typeof(get_first_last_id('2023-01'));
 pg_typeof
-----------
 integer[]
(1 row)

Of course, This function works well with any value.

> SELECT pg_typeof(2999999999);
 pg_typeof
-----------
 bigint
(1 row)

> SELECT pg_typeof(123);
 pg_typeof
-----------
 integer
(1 row)