I created the following table:
CREATE TABLE IF NOT EXISTS public.publications (
publication_id bigint NOT NULL DEFAULT nextval('eap_publications_publication_id_seq'::regclass),
title character varying(1000) COLLATE pg_catalog."default" NOT NULL,
author character varying(1000) COLLATE pg_catalog."default" NOT NULL,
type integer[] NOT NULL,
sys_publication_timestamp timestamp without time zone NOT NULL,
tags integer[] NOT NULL,
languages eap_control_vocabulary[],
isbn character varying(255) COLLATE pg_catalog."default",
url character varying(255) COLLATE pg_catalog."default",
thumbnail_url character varying(255) COLLATE pg_catalog."default",
target_audience integer[] NOT NULL,
topic integer[] NOT NULL,
related_projects integer[] NOT NULL,
featured boolean,
publication_status status,
sys_creat_ip_address inet,
sys_creat_timestamp timestamp without time zone NOT NULL,
sys_modif_ip_address inet,
sys_modif_timestamp timestamp without time zone,
CONSTRAINT publications_pkey PRIMARY KEY (publication_id)
);
And I made the following function to UPSERT:
CREATE OR REPLACE FUNCTION public.upsert_publication(
titlepub character varying(1000),
authorpub character varying(1000),
typepub integer[],
tagspub integer[],
languagespub text,
isbnpub character varying(255),
urlpub character varying(255),
thumbnail_urlpub character varying(255),
target_audiencepub integer[],
topicpub integer[],
related_projectspub integer[],
featuredpub boolean,
publication_statuspub status,
sys_creat_ip_addresspub inet,
sys_modif_ip_addresspub inet)
RETURNS jsonb
LANGUAGE 'plpgsql' AS
$BODY$
declare
pub_timestamp timestamp without time zone;
pub_creat_timestamp timestamp without time zone;
pub_modif_timestamp timestamp without time zone;
BEGIN
pub_timestamp :=current_timestamp;
pub_creat_timestamp :=current_timestamp;
pub_modif_timestamp :=current_timestamp;
INSERT INTO public.publications
VALUES (DEFAULT,titlepub, authorpub, typepub, pub_timestamp, tagspub, languagespub,isbnpub,urlpub,thumbnail_urlpub,
target_audiencepub, topicpub, related_projectspub, featuredpub, publication_statuspub, sys_creat_ip_addresspub, pub_creat_timestamp, sys_modif_ip_addresspub,pub_modif_timestamp )
ON CONFLICT (title, author)
DO
UPDATE SET title=EXCLUDED.title, author=EXCLUDED.author, type=EXCLUDED.type, sys_publication_timestamp=current_timestamp,
tags=EXCLUDED.tags, languages=EXCLUDED.languages,isbn=EXCLUDED.isbn,url=EXCLUDED.url,thumbnail_url=EXCLUDED.thumbnail_url,
target_audience=EXCLUDED.target_audience, topic=EXCLUDED.topic, related_projects=EXCLUDED.related_projects, featured=EXCLUDED.featured,
publication_status=EXCLUDED.publication_status, sys_creat_ip_address=EXCLUDED.sys_creat_ip_address, sys_creat_timestamp=current_timestamp,
sys_modif_ip_address=EXCLUDED.sys_modif_ip_address, sys_modif_timestamp=current_timestamp;
end;
$BODY$;
But every time I try to use it, I have the following error:
ERROR: column reference "title" is ambiguous
LINE 4: ON CONFLICT (title,author)
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
How can I make this reference less ambiguous? I try to add the name of the table in front of the "title" in the ON CONFLICT but that's just a syntax error.
I change the name of the inputs parameters as suggested. This as solve my issue, thanks!
There is a lot of noise. And the return type makes no sense. This should work:
CREATE OR REPLACE FUNCTION public.upsert_publication(
titlepub varchar(1000),
authorpub varchar(1000),
typepub integer[],
tagspub integer[],
languagespub eap_control_vocabulary[],
isbnpub varchar(255),
urlpub varchar(255),
thumbnail_urlpub varchar(255),
target_audiencepub integer[],
topicpub integer[],
related_projectspub integer[],
featuredpub boolean,
publication_statuspub status,
sys_creat_ip_addresspub inet,
sys_modif_ip_addresspub inet
)
RETURNS public.publications
LANGUAGE sql AS
$func$
INSERT INTO public.publications
title , author , type , sys_publication_timestamp, tags , languages , isbn , url , thumbnail_url , target_audience , topic , related_projects , featured , publication_status , sys_creat_ip_address , sys_creat_timestamp, sys_modif_ip_address , sys_modif_timestamp)
VALUES (titlepub, authorpub, typepub, CURRENT_TIMESTAMP, tagspub, languagespub, isbnpub, urlpub, thumbnail_urlpub, target_audiencepub, topicpub, related_projectspub, featuredpub, publication_statuspub, sys_creat_ip_addresspub, CURRENT_TIMESTAMP, sys_modif_ip_addresspub, CURRENT_TIMESTAMP)
ON CONFLICT (title, author) DO UPDATE
SET ( title, author, type, sys_publication_timestamp, tags, languages, isbn, url, thumbnail_url, target_audience, topic, related_projects, featured, publication_status, sys_creat_ip_address, sys_creat_timestamp, sys_modif_ip_address, sys_modif_timestamp)
= (EXCLUDED.title, EXCLUDED.author, EXCLUDED.type, CURRENT_TIMESTAMP, EXCLUDED.tags, EXCLUDED.languages, EXCLUDED.isbn, EXCLUDED.url, EXCLUDED.thumbnail_url, EXCLUDED.target_audience, EXCLUDED.topic, EXCLUDED.related_projects, EXCLUDED.featured, EXCLUDED.publication_status, EXCLUDED.sys_creat_ip_address, CURRENT_TIMESTAMP, EXCLUDED.sys_modif_ip_address, CURRENT_TIMESTAMP)
RETURNING *;
$func$;
The naming conflicts have already been resolved by using distinct input parameter names. See:
I don't see the need for PL/pgSQL. Nor the need for additional variables.
I strongly advise to spell out target column names for the persisted INSERT statement. Don't implicitly rely on the full list of table columns. This breaks easily with any changes to table columns later. (And possibly goes unnoticed.) Then you also don't need DEFAULT in the VALUES expression. Columns that are not targeted receive their column default per default.
The columns making up the UNIQUE constraint that conflicts are unchanged by definition, so you could omit those in the UPDATE part. But that doesn't make any difference. Postgres writes a new row version anyway. Same cost.
I formatted for easy visual control.