Re: Out of memory error - Mailing list pgsql-performance

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.