Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table publications(
  title text not null, 
  author text not null, 
  another_column int,
  CONSTRAINT publications_title_author_key UNIQUE (title,author) );

CREATE TABLE

CREATE OR REPLACE FUNCTION public.upsert_publications(
    title text,
    author text,
    another_column int)
RETURNS jsonb LANGUAGE 'plpgsql' AS $BODY$
BEGIN
  INSERT INTO public.publications
  VALUES (title,author,another_column)
  ON CONFLICT (title,author)
  DO UPDATE SET another_column=EXCLUDED.another_column;
END $BODY$;

CREATE FUNCTION

SELECT public.upsert_publications(
  title      => 'a' ::text,
  author     => 'a2'::text,
  another_column =>  0  ::int);

ERROR:  column reference "title" is ambiguous
LINE 3:   ON CONFLICT (title,author)
                      ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  INSERT INTO public.publications
  VALUES (title,author,another_column)
  ON CONFLICT (title,author)
  DO UPDATE SET another_column=EXCLUDED.another_column
CONTEXT:  PL/pgSQL function upsert_publications(text,text,integer) line 3 at SQL statement

BEGIN WORK;

select * from publications;

CREATE OR REPLACE FUNCTION public.upsert_publications(
    title text,
    author text,
    another_column int)
RETURNS jsonb LANGUAGE 'plpgsql' AS $BODY$
BEGIN
  INSERT INTO public.publications AS target --here's the alias
  VALUES (title,author,another_column)
  ON CONFLICT ((target.title),(target.author))--alias here, note the parentheses
    --left side of the SET assignment already assumes the target, 
    --don't use that target alias here, below, on the left of =
  DO UPDATE SET another_column=EXCLUDED.another_column;
  RETURN '{"award":"participation_award"}'::jsonb;
END $BODY$;

SELECT public.upsert_publications(
  title      => 'a' ::text,
  author     => 'a2'::text,
  another_column =>  0  ::int);

select * from publications;

SELECT public.upsert_publications(
  title      => 'a' ::text,
  author     => 'a2'::text,
  another_column =>  7  ::int);

select * from publications;

ROLLBACK;

BEGIN

title
author
another_column

SELECT 0

CREATE FUNCTION

upsert_publications

{"award": "participation_award"}

SELECT 1

title
author
another_column

a
a2
0

SELECT 1

upsert_publications

{"award": "participation_award"}

SELECT 1

title
author
another_column

a
a2
7

SELECT 1

ROLLBACK

START TRANSACTION;

select * from publications;

CREATE OR REPLACE FUNCTION public.upsert_publications(
    title text,
    author text,
    another_column int)
RETURNS jsonb LANGUAGE 'plpgsql' AS $BODY$
BEGIN
  INSERT INTO public.publications --no need for the alias
  VALUES (title,author,another_column)
  ON CONFLICT ON CONSTRAINT publications_title_author_key
  DO UPDATE SET another_column=EXCLUDED.another_column;
  RETURN '{"award":"participation_award"}'::jsonb;
END $BODY$;

SELECT public.upsert_publications(
  title      => 'a' ::text,
  author     => 'a2'::text,
  another_column =>  0  ::int);

select * from publications;

SELECT public.upsert_publications(
  title      => 'a' ::text,
  author     => 'a2'::text,
  another_column =>  7  ::int);

select * from publications;

ROLLBACK;

START TRANSACTION

title
author
another_column

SELECT 0

CREATE FUNCTION

upsert_publications

{"award": "participation_award"}

SELECT 1

title
author
another_column

a
a2
0

SELECT 1

upsert_publications

{"award": "participation_award"}

SELECT 1

title
author
another_column

a
a2
7

SELECT 1

ROLLBACK