Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(
  my_column text not null unique, 
  another_column int);

CREATE TABLE

BEGIN TRANSACTION;

select * from test;

CREATE OR REPLACE FUNCTION public.upsert_test(
    my_column text,
    another_column int)
RETURNS jsonb LANGUAGE SQL AS $BODY$
  INSERT INTO public.test as target --here
  VALUES (my_column,another_column)
  ON CONFLICT ((target.my_column))
  DO UPDATE SET another_column=EXCLUDED.another_column
  RETURNING '{"award":"participation_award"}'::jsonb;
$BODY$;

SELECT public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  0 ::int);

select * from test;

SELECT public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  7 ::int);

select * from test;

ROLLBACK;

BEGIN

my_column
another_column

SELECT 0

CREATE FUNCTION

upsert_test

{"award": "participation_award"}

SELECT 1

my_column
another_column

a
0

SELECT 1

upsert_test

{"award": "participation_award"}

SELECT 1

my_column
another_column

a
7

SELECT 1

ROLLBACK

BEGIN TRANSACTION;

select * from test;

CREATE OR REPLACE PROCEDURE public.upsert_test(
    my_column text,
    another_column int)
LANGUAGE 'plpgsql' AS $BODY$
BEGIN
  INSERT INTO public.test as target --here
  VALUES (my_column,another_column)
  ON CONFLICT ((target.my_column))
  DO UPDATE SET another_column=EXCLUDED.another_column;
  --RETURN '{"award":"participation_award"}'::jsonb;
END $BODY$;

CALL public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  0 ::int);

select * from test;

CALL public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  7 ::int);

select * from test;

ROLLBACK;

BEGIN

my_column
another_column

SELECT 0

CREATE PROCEDURE

CALL

my_column
another_column

a
0

SELECT 1

CALL

my_column
another_column

a
7

SELECT 1

ROLLBACK

BEGIN TRANSACTION;

select * from test;

CREATE OR REPLACE PROCEDURE public.upsert_test(
    my_column text,
    another_column int)
LANGUAGE SQL AS $BODY$
  INSERT INTO public.test as target --here
  VALUES (my_column,another_column)
  ON CONFLICT ((target.my_column))
  DO UPDATE SET another_column=EXCLUDED.another_column;
$BODY$;

CALL public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  0 ::int);

select * from test;

CALL public.upsert_test(
  my_column      => 'a'::text,
  another_column =>  7 ::int);

select * from test;

ROLLBACK;

BEGIN

my_column
another_column

SELECT 0

CREATE PROCEDURE

CALL

my_column
another_column

a
0

SELECT 1

CALL

my_column
another_column

a
7

SELECT 1

ROLLBACK

BEGIN TRANSACTION;

select * from test;

prepare upsert_test(
    text,
    int)
AS
  INSERT INTO public.test AS target
  VALUES ($1,$2)
  ON CONFLICT ((target.my_column))
  DO UPDATE SET another_column=EXCLUDED.another_column
;

EXECUTE upsert_test('a'::text, 0 ::int);

select * from test;

EXECUTE upsert_test('a'::text, 7 ::int);

select * from test;

ROLLBACK;

BEGIN

my_column
another_column

SELECT 0

PREPARE

INSERT 0 1

my_column
another_column

a
0

SELECT 1

INSERT 0 1

my_column
another_column

a
7

SELECT 1

ROLLBACK