Column reference "workspace_id" is ambiguous in PL/pgSQL function

Question

In this code, when I run:

CREATE TABLE IF NOT EXISTS setup_workspace_result (
  path TEXT,
  workspace_id BIGINT
);

DROP FUNCTION IF EXISTS public.setup_workspace;

CREATE OR REPLACE FUNCTION setup_workspace(refresh_token TEXT)
RETURNS setup_workspace_result
AS $$
DECLARE
  user_email TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'email');
  workspace_name TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'hd');
  name TEXT :=  ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'name');
  is_admin BOOLEAN := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'isAdmin');
  workspace_id BIGINT;
  user_id BIGINT;
  result setup_workspace_result;
BEGIN
  -- Check if user already exists
  SELECT users.id, users.workspace_id INTO user_id, workspace_id FROM users WHERE email = user_email;
  IF user_id IS NOT NULL THEN
    -- User already exists, return workspace_id
    UPDATE service_google 
    SET token = refresh_token
    WHERE service_google.workspace_id = workspace_id;

    result.workspace_id := workspace_id;
    result.path := 'login';
  ELSE
    -- Insert data into tables
    INSERT INTO users (email, name)
    VALUES (user_email, name) RETURNING id INTO user_id;

    INSERT INTO workspaces (domain_name, created_by)
    VALUES (workspace_name, user_id) RETURNING id INTO workspace_id;

    INSERT INTO super_admins (workspace_id, user_id)
    VALUES (workspace_id, user_id);

    result.workspace_id := workspace_id;
    result.path := 'signup';
  END IF;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

I get the following error:

{
  code: '42702',
  details: 'It could refer to either a PL/pgSQL variable or a table column.',
  hint: null,
  message: 'column reference "workspace_id" is ambiguous'
}

Any idea why I am getting this and how I can fix it?

Answer 1

You have a column named workspace_id, and you declared a variable of the same name. That's the recipe for naming collisions. Don't do that.

Use variable names that are distinct from all involved column names if at all possible. I like to prefix variable names with underscore, like: _workspace_id. That's just one of many ways to fix this.
Table-qualified column names in the PL/pgSQL code block also avoid conflicts. Never a bad idea.

Related:

• Postgres function NULL value for row that references NEW

• How to return result of a SELECT inside a function in PostgreSQL?

Fix race condition

After fixing the error that surfaced, there are more, less obvious problems in your function.

Basically, it's a classic case of "SELECT or INSERT" and the current implementation is prone to race conditions. Read this first:

• Is SELECT or INSERT in a function prone to race conditions?

Then consider this rewrite:

CREATE OR REPLACE FUNCTION setup_workspace(refresh_token text, OUT result setup_workspace_result)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _user_email       text;
   _workspace_name   text;
   _name             text;
-- _is_admin         boolean;  -- unused
   _workspace_id     bigint;
   _user_id          bigint;
BEGIN
   SELECT js ->> 'email', js ->> 'hd', js ->> 'name'  -- , js ->> 'isAdmin'
   INTO   _user_email, _workspace_name, _name  -- , _is_admin
   FROM  (SELECT current_setting('request.jwt.claims', true)::json) t(js);

   IF _user_email IS NULL THEN
      RAISE EXCEPTION 'Custom variable "request.jwt.claim" not set, or email missing!';  -- or similar
   END IF;

   LOOP
      -- Check if user already exists
      SELECT u.id    , u.workspace_id
      INTO   _user_id, _workspace_id
      FROM   users u
      WHERE  u.email = _user_email;

      IF FOUND THEN
         -- User already exists, return workspace_id
         UPDATE service_google  s
         SET    token = refresh_token
         WHERE  s.workspace_id = _workspace_id;

         result.workspace_id := workspace_id;
         result.path := 'login';

         EXIT;
      END IF;

      -- Insert data into tables
      INSERT INTO users (email, name)
      VALUES (_user_email, _name)
      ON     CONFLICT DO NOTHING   -- narrow down to specific unique violation?
      RETURNING id
      INTO _user_id;

      IF FOUND THEN
         -- Insert data into tables
         INSERT INTO users (email, name)
         VALUES (_user_email, _name)
         RETURNING id
         INTO _user_id;

         INSERT INTO workspaces (domain_name, created_by)
         VALUES (_workspace_name, _user_id)
         RETURNING id
         INTO   _workspace_id;

         INSERT INTO super_admins (workspace_id, user_id)
         VALUES (_workspace_id, _user_id);

         result.workspace_id := _workspace_id;
         result.path := 'signup';
         
         EXIT;
      END IF;
   END LOOP;
END
$func$;

Also fixes your naming collision problem and adds a couple other improvements.

Answer 2

Many databases, including postgres, get confused when variable names clash with column names. To avoid this, I prefix variable names with underscore so they never clash, like this:

DECLARE
  _user_email TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'email');
  _workspace_name TEXT := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'hd');
  _name TEXT :=  ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'name');
  _is_admin BOOLEAN := ((current_setting('request.jwt.claims'::text, TRUE))::JSON ->> 'isAdmin');
  _workspace_id BIGINT;
  _user_id BIGINT;
  _result setup_workspace_result;

It also makes the code easier to read because variables stand out.

You can do this with different styles as you prefer, such as two underscores __workspace_id or v_ like v_workspace_id to fit in better with naming standards your company might have.