Hi Thomas, v_message is of composite data type r_log_message and it's definition is as shown below.
postgres=# \d r_log_message; Composite type "public.r_log_message" Column | Type | Collation | Nullable | Default -------------+-------------------------+-----------+----------+--------- column_name | character varying(30) | | | oldvalue | character varying(4000) | | | newvalue | character varying(4000) | | | Regards, Aditya. On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer <sham...@gmx.net> wrote: > aditya desai schrieb am 24.11.2021 um 07:25: > > Thanks Tom. However I could not find any solution to achieve the given > requirement. I have to take all values in the temp table and assign it to > an array variable to pass it to the audit procedure as shown below. Can you > please advise ? > > > > CREATE OR REPLACE FUNCTION call_insert_info( > > > > ) RETURNS void AS $$ > > DECLARE > > v_message r_log_message[]; > > OLDVALUE1 varchar(4000); > > BEGIN > > drop table if exists changedinfo > > create temp table changedinfo(colName varchar(100), oldValue > varchar(4000), newValue varchar(4000)); > > insert into changed infot select 'empName', OLD.empName, > NEW.empName from employee; > > insert into changed infot select 'location', OLD.location, > NEW.location from employee; > > > > > > v_message:= array(select '(' || columname || ',' || oldvalue || ',' || > newvalue ||')' from changedinfo); > > perform insert_info(v_message); > > raise notice '%',v_message; > > END; > > $$ LANGUAGE plpgsql; > > > You don't need a temp table for that. You can create the array directly > from the new and old records: > > v_message := array[concat_ws(',', 'empName', old.empname, > new.empname), concat_ws(',', 'location', old.location, new.location)]; > > Although nowadays I would probably pass such an "structure" as JSON > though, not as a comma separated list. > > > >