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
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
orINSERT
" and the current implementation is prone to race conditions. Read this first:Then consider this rewrite:
Also fixes your naming collision problem and adds a couple other improvements.
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:
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
orv_
likev_workspace_id
to fit in better with naming standards your company might have.