skip to Main Content

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?

2

Answers


  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:

    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:

    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.

    Login or Signup to reply.
  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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search