Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type status as enum('in_progress','published','retracted');
create type eap_control_vocabulary as enum('a','b','c');

CREATE TABLE IF NOT EXISTS public.publications
(   publication_id bigserial,
    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)
)

CREATE TYPE



CREATE TYPE



CREATE TABLE


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 :=current_timestamp;
        pub_creat_timestamp timestamp without time zone :=current_timestamp;
        pub_modif_timestamp timestamp without time zone :=current_timestamp;
BEGIN
INSERT INTO public.publications as target
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 
    target.title=EXCLUDED.title, target.author=EXCLUDED.author, 
    target.type=EXCLUDED.type,   target.sys_publication_timestamp=current_timestamp,
    target.tags=EXCLUDED.tags,   target.languages=EXCLUDED.languages,
    target.isbn=EXCLUDED.isbn,   target.url=EXCLUDED.url,
    target.thumbnail_url=EXCLUDED.thumbnail_url, 
    target.target_audience=EXCLUDED.target_audience,    
    target.topic=EXCLUDED.topic, 
    target.related_projects=EXCLUDED.related_projects,  
    target.featured=EXCLUDED.featured, 
    target.publication_status=EXCLUDED.publication_status, 
    target.sys_creat_ip_address=EXCLUDED.sys_creat_ip_address, 
    target.sys_creat_timestamp=current_timestamp, 
    target.sys_modif_ip_address=EXCLUDED.sys_modif_ip_address, 
    target.sys_modif_timestamp=current_timestamp;

END $BODY$;

CREATE FUNCTION


SELECT public.upsert_publication(
    titlepub    =>'titlepub character varying(1000)'::character varying(1000),
    authorpub   =>'authorpub character varying(1000)'::character varying(1000),
    typepub     =>'{1,2,3}'::integer[],
    tagspub     =>'{4,5,6}'::integer[],
    languagespub=>'a'::text,
    isbnpub     =>'character varying(255)'::character varying(255),
    urlpub      =>'character varying(255)'::character varying(255),
    thumbnail_urlpub    =>'character varying(255)'::character varying(255),
    target_audiencepub  =>'{7,8,9}'::integer[],
    topicpub    =>'{10,11,12}'::integer[],
    related_projectspub =>'{13,14,15}'::integer[],
    featuredpub =>'T'::boolean,
    publication_statuspub   =>'published'::status,
    sys_creat_ip_addresspub =>'127.0.0.1'::inet,
    sys_modif_ip_addresspub =>'192.168.0.1'::inet);

ERROR:  column "languages" is of type eap_control_vocabulary[] but expression is of type text
LINE 3:         pub_timestamp, tagspub, languagespub,
                                        ^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO public.publications as target
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 
    target.title=EXCLUDED.title, target.author=EXCLUDED.author, 
    target.type=EXCLUDED.type,   target.sys_publication_timestamp=current_timestamp,
    target.tags=EXCLUDED.tags,   target.languages=EXCLUDED.languages,
    target.isbn=EXCLUDED.isbn,   target.url=EXCLUDED.url,
    target.thumbnail_url=EXCLUDED.thumbnail_url, 
    target.target_audience=EXCLUDED.target_audience,    
    target.topic=EXCLUDED.topic, 
    target.related_projects=EXCLUDED.related_projects,  
    target.featured=EXCLUDED.featured, 
    target.publication_status=EXCLUDED.publication_status, 
    target.sys_creat_ip_address=EXCLUDED.sys_creat_ip_address, 
    target.sys_creat_timestamp=current_timestamp, 
    target.sys_modif_ip_address=EXCLUDED.sys_modif_ip_address, 
    target.sys_modif_timestamp=current_timestamp
CONTEXT:  PL/pgSQL function upsert_publication(character varying,character varying,integer[],integer[],text,character varying,character varying,character varying,integer[],integer[],integer[],boolean,status,inet,inet) line 6 at SQL statement