Решить вопрос можно двумя способами.
Первый
WITH user_data AS (INSERT INTO public.users(first_name, second_name, password)
VALUES ($1, $2, $3) RETURNING id, first_name, second_name),
user_phone AS (INSERT INTO public.user_phone_number(user_id, phone_number)
VALUES ((SELECT id FROM user_data), $4) RETURNING user_id, phone_number)
SELECT ud.id, ud.first_name, ud.second_name, up.phone_number
FROM user_data AS ud INNER JOIN user_phone AS up
ON up.user_id = ud.id
Второй
DO $$
DECLARE tableId bigint;
BEGIN
INSERT INTO public.users(first_name, second_name, password) VALUES ($1, $2, $3) RETURNING id INTO tableId;
INSERT INTO public.user_phone_number(user_id, phone_number) VALUES (tableId, $4);
COMMIT;
END $$;