Postgres 14

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

CREATE TABLE tbl (company text, profession text, year int);
INSERT INTO tbl VALUES
  ('Google',  'Programmer', 2000)
, ('Google',  'Sales',      2000)
, ('Google',  'Sales',      2001)
, ('Google',  'Sales',      2002)
, ('Google',  'Sales',      2004)
, ('Mozilla', 'Sales',      2002)
;

CREATE TABLE



INSERT 0 6


CREATE OR REPLACE FUNCTION f_periods()
  RETURNS TABLE (company text, profession text, years int[])
  LANGUAGE plpgsql AS
$func$
DECLARE
   r  tbl; -- use table type as row variable
   r0 tbl;
BEGIN
   FOR r IN
      SELECT * FROM tbl t ORDER BY t.company, t.profession, t.year
   LOOP
      IF ( r.company,  r.profession,  r.year)
      <> (r0.company, r0.profession, r0.year + 1) THEN -- not true for first row

         RETURN QUERY
         SELECT r0.company, r0.profession, years; -- output row

         years := ARRAY[r.year];     -- start new array
      ELSE
         years := years || r.year;   -- add to array - year can be NULL, too
      END IF;

      r0 := r;                       -- remember last row
   END LOOP;

   RETURN QUERY                      -- output last iteration
   SELECT r0.company, r0.profession, years;
END
$func$;

CREATE FUNCTION


SELECT * FROM f_periods();


company
profession
years


Google
Programmer
{2000}

Google
Sales
{2000,2001,2002}

Google
Sales
{2004}

Mozilla
Sales
{2002}



SELECT 4

company	profession	years
Google	Programmer	{2000}
Google	Sales	{2000,2001,2002}
Google	Sales	{2004}
Mozilla	Sales	{2002}