Avoiding Ambiguous Postgres Variables Without Prefixes

Using Postgres functions with your hosting provider? Ignore what you’ve heard: Name your input variables whatever you want.

If you use Supabase, Vercel Postgres, Neon Serverless Postgres or any of the many hosting providers, you will have a need to write custom Postgres functions, especially when you deal with roles and transactions. However, you can name your input variables whatever you want, despite what you have read.

Common Errors

parameter name "id" used more than once

Never see this error again.

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

Or a complex JSON error statement like this.

Prefixes

Let’s say we want to select a post by id:

DROP FUNCTION IF EXISTS get_post_by_id;
CREATE OR REPLACE FUNCTION get_post_by_id(p_id uuid)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY SELECT * FROM posts WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

Normally you see something like this where the id field has a prefix like p_, although this could be anything. I hate prefixes, and there are ways to avoid them.

Supabase Example

If I’m using Supabase, I don’t want to pass my id like this:

// normally you will already have an id variable from somewhere else in your code
const id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

const { data, error } = await supabase.rpc('get_post_by_id', { p_id: id });

When I should be able to just do this:

const { data, error } = await supabase.rpc('get_post_by_id', { id });

I know, I know, I’m splitting hairs here. However, this can save you lots of time by using some good practices.

1. Use Aliases

In the function, you should always be using this alias. This creates a lot of clarity when your functions get complex. Each command has different rules for alias.

Select

SELECT p.* FROM posts p WHERE p.id = '0x123';

The posts AS p is also possible, but why add another word when you don’t have to. You also don’t need the extra p in p.*—this is just to clarify you can add the prefix in the select statement.

Insert

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
RETURNING p.*;

Notice you do need the AS in the insert statements. Again, the extra p. is unnecessary at the end.

If you use ON CONFLICT, you must use the constraint name, as unlike the value in SET, the conflict WILL conflict with your variable.

DON’T DO THIS!

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
ON CONFLICT (id) DO UPDATE

Instead, use the constraint name:

RETURN QUERY
INSERT INTO posts AS p (title, content)
VALUES ('title1', 'content1')
ON CONFLICT ON CONSTRAINT posts_pkey DO UPDATE

Update

UPDATE posts p SET title = 'something' WHERE p.id = '0x123';

Here, notice you can’t use an alias within the set statement. No worries, as this will not conflict with anything. If you’re returning the data, remember to use an alias there too:

RETURN QUERY
UPDATE posts p
SET title = 'new title'
WHERE p.id = '0x123'
RETURNING p.id, p.title;

Delete

RETURN QUERY
DELETE FROM posts p WHERE p.id = '0x123'
RETURNING p.id;

And delete works as expected since there are no inputs.

2. Use Function Names

What a lot of people don’t realize, and this is perhaps the best trick in this post, is that you can use the function name with the variable name as an alias. After all, it is the technical full name.

SELECT * FROM posts p WHERE p.id = get_post_by_id.id;

This means the final working function looks like this:

DROP FUNCTION IF EXISTS get_post_by_id;
CREATE OR REPLACE FUNCTION get_post_by_id(id uuid)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY SELECT * FROM posts p WHERE p.id = get_post_by_id.id;
END;
$$ LANGUAGE plpgsql;

And you could call it as expected.

Update Example

Here is an update example to show more complexity:

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING *;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

Return and Parameter Conflicts

But, we have one last problem to figure out. What if the input and output are the same name? AKA, the parameters and return values have variables that use the same name?

If we’re returning a SETOF posts, no problem, but when we need to return a custom table, this gets muddy. This is because the return type is really just a function parameter as well.

DON’T DO THIS!

CREATE FUNCTION test(id uuid)
RETURNS TABLE(id uuid) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT posts.id FROM posts WHERE posts.id = test.id;
END;
$$;

This is actually the same thing as this using IN and OUT declarations:

DON’T DO THIS!

CREATE FUNCTION test(IN id uuid, OUT id uuid)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT posts.id FROM posts WHERE posts.id = test.id;
END;
$$;

So, as you can see, we can’t have two parameters with the same name. Ignore the uselessness of this function. 😄

1. Return a JSON Type

You could just return a JSON type (always use jsonb unless you know otherwise).

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF jsonb AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING jsonb_build_object('id', p.id, 'title', p.title);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

2. Use Custom Types

Or you could use your own custom type. This is useful when you have more than one function that returns the same type of data.

CREATE TYPE post_simple AS (
  id uuid,
  title text
);

DROP FUNCTION IF EXISTS update_post_title;
CREATE OR REPLACE FUNCTION update_post_title(id uuid, title text)
RETURNS SETOF post_simple AS $$
BEGIN
  RETURN QUERY
  UPDATE posts p
  SET title = update_post_title.title
  WHERE p.id = update_post_title.id
  RETURNING p.id, p.title;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

Cheating…

And finally, if all else fails, Postgres has some fallback for Variable Conflicts. Simply put the necessary conflict eliminator before the declaration of your function.

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

I listed this last, but I am a firm believer that you should not need them. Structure your functions correctly!

Whoever tells you to use prefixes in function parameters is wrong. Just create your functions the right way.