how do I go about ensuring that the recently generated id is returned when we insert a record via a PostgreSQL View's trigger's function?

Question

Here is info about our technical development environment :

PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit

Here are the various code snippets associated with the various database objects in my PostgreSQL database:

CREATE TABLE IF NOT EXISTS public.movies (

id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 )

                      , name text COLLATE pg_catalog."default"

                      , director text COLLATE pg_catalog."default"

                      , cost integer NOT NULL

                      , revenue integer NOT NULL );

 CREATE OR REPLACE VIEW public.vw_movies_info AS SELECT m_table.id,

    m_table.name,

    m_table.director,

    m_table.cost,

    m_table.revenue , 

   FROM movies m_table;

  CREATE OR REPLACE FUNCTION iud_vw_movies_info_func() RETURNS TRIGGER

 AS $$ BEGIN

IF TG_OP = 'INSERT' then
    raise notice 'INSERT trigger, NEW = [%]', NEW;
            Insert into movies(
                      , name
                      , director
                      , cost
                      , revenue)
               Values( NEW.name
                        , NEW.director
                      , NEW.cost
                      , NEW.revenue );
                      
                                                                                                                                                                                                                                                                                                                                                                            
ELSIF TG_OP = 'UPDATE' then 
    raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;
                            Update movies ee
                                   Set name = NEW.name
                                                  where ee.id = NEW.id;
ELSE
    raise notice 'DELETE trigger, OLD = [%]', OLD;
                            Delete from movies ee 
                                      where ee.id = OLD.id;
END IF;
RETURN COALESCE(NEW,OLD); END; $$ LANGUAGE plpgsql;

 CREATE OR REPLACE TRIGGER vw_movies_info_trigger INSTEAD OF INSERT OR
 UPDATE OR DELETE ON vw_movies_info FOR EACH ROW EXECUTE Function
 iud_vw_movies_info_func();

It is important to note that in our iud_vw_movies_info_func() function, we have the RETURN COALESCE(NEW,OLD).

When I run the following insert sql code on the vw_movies_info View :

 Insert into vw_ movies_info(
                      , name
                      , director
                      , cost
                      , revenue)
               Values( ‘Chronicles of Naria’
                        ,  ‘Andrew Adamson’
                      , 500000
                      , 1000000 );

It is important to note that in our iud_vw_movies_info_func() function, we have the RETURN COALESCE(NEW,OLD). When we running an insert sql code for a new movie entry, the NEW.id will Not contain the recently generated id value because it contains the NEW values that being inserted Therefore, how do I go about ensuring that the recently generated id of the aforementioned insert sql code execution is returned?

crazyTech · Answer 1 · 2022-11-02T20:26:32.333

After the Insert sql code, I added RETURNING id INTO NEW.ID; , and it worked.

  CREATE OR REPLACE FUNCTION iud_vw_movies_info_func() RETURNS TRIGGER

  AS $$ BEGIN

 IF TG_OP = 'INSERT' then
raise notice 'INSERT trigger, NEW = [%]', NEW;
        Insert into movies(
                  , name
                  , director
                  , cost
                  , revenue)
           Values( NEW.name
                    , NEW.director
                  , NEW.cost
                  , NEW.revenue )

                RETURNING id INTO NEW.ID;

ELSIF TG_OP = 'UPDATE' then 
raise notice 'UPDATE trigger, OLD = [%], NEW = [%]', OLD, NEW;
                        Update movies ee
                               Set name = NEW.name
                                              where ee.id = NEW.id; ELSE
raise notice 'DELETE trigger, OLD = [%]', OLD;
                        Delete from movies ee 
                                  where ee.id = OLD.id; END IF; RETURN COALESCE(NEW,OLD); END; $$ LANGUAGE plpgsql;

how do I go about ensuring that the recently generated id is returned when we insert a record via a PostgreSQL View's trigger's function?

1 Answers1