Column reference 'updated_at' is ambiguous in Postgres

I'm trying to upsert a post:

DROP FUNCTION IF EXISTS upsert_post;
CREATE OR REPLACE FUNCTION upsert_post(
    title text,
    content text,
    short_id text DEFAULT NULL,
    image text DEFAULT NULL,
    status post_status DEFAULT 'draft',
    updated_at timestamptz DEFAULT NULL,
    published_at timestamptz DEFAULT NULL,
    created_at timestamptz DEFAULT NULL
)
RETURNS SETOF posts
AS $$
DECLARE
    post_id uuid;
BEGIN
	-- Get the id based on the provided short_id
    SELECT id INTO post_id FROM posts as p 
    WHERE p.short_id = upsert_post.short_id;

    -- Upsert the post and return the changed row
    RETURN QUERY
    INSERT INTO posts as i (
        id,
        status,
        title,
        content,
        image,
        updated_at,
        published_at,
        created_at
    )
    VALUES (
        COALESCE(post_id, uuid_generate_v4()),
        upsert_post.status,
        upsert_post.title, 
        upsert_post.content,
        upsert_post.image,
        COALESCE(upsert_post.updated_at, now()),
        COALESCE(upsert_post.published_at, now()),
        COALESCE(upsert_post.created_at, now())
    )
    ON CONFLICT (id, updated_at) DO UPDATE
    SET
        title = EXCLUDED.title,
        content = EXCLUDED.content,
        image = COALESCE(EXCLUDED.image, i.image),
        updated_at = COALESCE(EXCLUDED.updated_at, i.updated_at),
        published_at = COALESCE(EXCLUDED.published_at, i.published_at),
        created_at = COALESCE(EXCLUDED.created_at, i.created_at)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

But I get the error:

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

I have used aliases everywhere I know possible. How can I get this working without renaming my upsert_post parameters with a prefix i_ or something similar?

i needed to adept the function, as user typ and function are supplied and create table would also help much

In short you should never name your variables like column names, so that postgres has no more problem differentiate them

the idea behind short_id i can only guess. but i think you should also enter it, when inserting a new row

CREATE TABLe posts (id text PRIMARY KEY,    
    status text DEFAULT 'draft',
  title text,
  content text,
    short_id text DEFAULT NULL,
    image text DEFAULT NULL,
    
    updated_at timestamptz DEFAULT NULL,
    published_at timestamptz DEFAULT NULL,
    created_at timestamptz DEFAULT NULL,
  UNIQUE (id, updated_at)
)

> status
> CREATE TABLE
>

CREATE OR REPLACE FUNCTION upsert_post(
    f_title text,
    f_content text,
    f_short_id text DEFAULT NULL,
    f_image text DEFAULT NULL,
    f_status text DEFAULT 'draft',
    f_updated_at timestamptz DEFAULT NULL,
    f_published_at timestamptz DEFAULT NULL,
    f_created_at timestamptz DEFAULT NULL
)
RETURNS SETOF posts
AS $$
DECLARE
    post_id uuid;
BEGIN
    -- Get the id based on the provided short_id
    SELECT id INTO post_id FROM posts as p 
    WHERE p.short_id = f_short_id;

    -- Upsert the post and return the changed row
    RETURN QUERY
    INSERT INTO posts as i (
        id,
        status,
        title,
        content,
        image,
        updated_at,
        published_at,
        created_at
    )
    VALUES (
        COALESCE(post_id, gen_random_uuid ()),
        f_status,
        f_title, 
        f_content,
        f_image,
        COALESCE(f_updated_at, now()),
        COALESCE(f_published_at, now()),
        COALESCE(f_created_at, now())
    )
    ON CONFLICT (id, updated_at) DO UPDATE
    SET
        title = EXCLUDED.title,
        content = EXCLUDED.content,
        image = COALESCE(EXCLUDED.image, i.image),
        updated_at = COALESCE(EXCLUDED.updated_at, i.updated_at),
        published_at = COALESCE(EXCLUDED.published_at, i.published_at),
        created_at = COALESCE(EXCLUDED.created_at, i.created_at)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

> status
> CREATE FUNCTION
>

SELECT
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
AND
    routine_schema = 'public';

SELECT upsert_post('A'::TEXT,'b'::TEXT,'C'::TEXT,'D'::TEXT,'draft'::TEXT,NOW(),NOW(), NOW()) 

SELECT * FROM posts

fiddle

My issue here was the input parameter conflicting with the ON CONFLICT parameter.

There are three real answers I finally found.

1. #variable_conflict

Postgres has three variables to use for this use case:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

But I got it working thanks to the Variable Substitution Docs provided above:

RETURNS SETOF posts
AS $$
#variable_conflict use_column
DECLARE
    post_id uuid;
BEGIN

2. ON CONFLICT ON CONSTRAINT

The problem with my code above was the ON CONFLICT not allowing you to use aliases with it. If you know a way, please let me know. Otherwise, I simply just used the name of the compound constraint with ON CONSTRAINT instead.

 ON CONFLICT ON CONSTRAINT posts_pkey DO UPDATE

Edit: You could also name the constraint anything you want, in the schema:

CREATE TABLE posts (
  ...
  CONSTRAINT id_updated_at_pk PRIMARY KEY (id, updated_at)
  ...
);

3. Create a sub function

Basically you could just create a function _upsert_post() which does take prefixed parameters. Then just pass your non prefixed parameters into this function:

upsert_post

RETURN QUERY SELECT * FROM _upsert_post(id, status, title...);

To say that you have to use prefixes is simply not true. In my case, the user would have to change the frontend code to use different parameters than the backend code. This can be important for teams.