Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE name_value_pair AS (name text, value text);

CREATE TYPE

-- Use SELECT instead of a ROW constructor to get implicit cast:
CREATE OR REPLACE FUNCTION test1()
  RETURNS SETOF name_value_pair AS
$func$
BEGIN
   RETURN NEXT (SELECT t FROM (SELECT 'email', 'foo@example.com') AS t);
   RETURN NEXT (SELECT t FROM (SELECT 'user_id', 'abc123') AS t);
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test1();

CREATE FUNCTION

name
value

email
foo@example.com

user_id
abc123

SELECT 2

-- That's simpler without the composite type:
CREATE OR REPLACE FUNCTION test2()
  RETURNS TABLE (name text, value text) AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test2();

CREATE FUNCTION

name
value

email
foo@example.com

user_id
abc123

SELECT 2

-- ... but you can still use the composite to define the return type:
CREATE OR REPLACE FUNCTION test3()
  RETURNS SETOF name_value_pair AS
$func$
BEGIN
   RETURN QUERY SELECT 'email', 'foo@example.com';
   RETURN QUERY SELECT 'user_id', 'abc123';
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test3();

CREATE FUNCTION

name
value

email
foo@example.com

user_id
abc123

SELECT 2

-- While it's that simple, a plain VALUES expression in a plain SQL function is shorter and faster:
CREATE OR REPLACE FUNCTION test4()
  RETURNS SETOF name_value_pair AS
$func$
VALUES
  ('email', 'foo@example.com')
, ('user_id', 'abc123');
$func$  LANGUAGE sql IMMUTABLE;

SELECT * FROM test4();

CREATE FUNCTION

name
value

email
foo@example.com

user_id
abc123

SELECT 2

-- Or if it has to be plpgsql:
CREATE OR REPLACE FUNCTION test5()
  RETURNS SETOF name_value_pair AS
$func$
BEGIN
RETURN QUERY
VALUES
  ('email', 'foo@example.com')
, ('user_id', 'abc123');
END
$func$  LANGUAGE plpgsql IMMUTABLE;

SELECT * FROM test5();

CREATE FUNCTION

name
value

email
foo@example.com

user_id
abc123

SELECT 2