postgresql does not return value on select * after using CTE

Question

I try to create a new user and push the new user id to auth table with their google id.

Then i want to return the new user, but even though i use select * no value is returned. (but the user gets created and auth row in auth table also is created)

Here is the query:

WITH returned_user_id AS (
    INSERT INTO users (display_name, avatar)
    VALUES ('AAA', 'AAA')
    RETURNING id
),
    inserted_auth AS (
        INSERT INTO auth (user_id, google_id)
        SELECT id, '31231231'
        FROM returned_user_id
    )
SELECT *
FROM users
WHERE id = (SELECT user_id FROM auth WHERE google_id = '31231231');

and here is the way i use it in my backend:

   //CREATE NEW ACCOUNT
  async create(googleProfile:GoogleProfile):Promise<DataSingle>{
      const {googleId, displayName, avatar} = googleProfile

      try {
          const {rows} = await pool.query(`


         WITH returned_user_id AS (
        INSERT INTO users (display_name, avatar)
        VALUES ($1, $2) 
        RETURNING id
    ),
        inserted_auth AS (
        INSERT INTO auth (user_id, google_id) 
        SELECT id, $3
        FROM returned_user_id
        )
    SELECT *
    FROM users
    WHERE id = (SELECT user_id FROM auth WHERE google_id = $3);

    `, [displayName, avatar, googleId])

      return {
          data: snakeToCamel(rows)[0],
          error:null,
          status: 201
      };
  }catch (err){
      console.error(err)
      return {
          data: null,
          error: "Could not create the user",
          status: 400
      };
  }

},

Answer 1

Try directly take inserted user Id

WITH returned_user_id AS (
    INSERT INTO users (display_name, avatar)
    VALUES ('AAA', 'AAA')
    RETURNING id
),
    inserted_auth AS (
        INSERT INTO auth (user_id, google_id)
        SELECT id, '31231231'
        FROM returned_user_id
       RETURNING user_id
    )
select user_id from inserted_auth
  ;