Subject: Re: Out of memory error
To: pgsql-performance@lists.postgresql.org
References: 
 <CAN0SRDGg3-wH+y7bNvJQa7cdR4wY+7AqoeHbV0urVfBCszSCqg@mail.gmail.com>
 <97240.1637733158@sss.pgh.pa.us>
 <CAN0SRDGZeRyxcB-LWKjNQSi0O3tcK_xKPZdhaLvj_8+sbNPoBA@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <4af2ff21-1140-ccbd-5adf-926dc816782f@gmx.net>
Date: Wed, 24 Nov 2021 07:31:08 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAN0SRDGZeRyxcB-LWKjNQSi0O3tcK_xKPZdhaLvj_8+sbNPoBA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/4af2ff21-1140-ccbd-5adf-926dc816782f%40gmx.net>
Precedence: bulk

aditya desai schrieb am 24.11.2021 um 07:25:
> Thanks Tom. However I could not find any solution to achieve the given r=
equirement. I have to take all values in the temp table and assign it to a=
n array variable to pass it to the audit procedure as shown below. Can you=
 please advise ?=C2=A0
>
> CREATE OR REPLACE FUNCTION call_insert_info(
> =C2=A0 =C2=A0=C2=A0
> ) RETURNS void AS $$
> =C2=A0 =C2=A0 DECLARE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_message r_log_message[];
> OLDVALUE1 varchar(4000);
> =C2=A0 =C2=A0 BEGIN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 drop table if exists changedin=
fo
> =C2=A0 =C2=A0 create temp table changedinfo(colName varchar(100), oldVal=
ue varchar(4000), newValue varchar(4000));
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 insert into changed infot sele=
ct 'empName', OLD.empName, NEW.empName from employee;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 insert into changed infot sele=
ct 'location', OLD.location, NEW.location from employee;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
> =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
> v_message:=3D=C2=A0 =C2=A0array(select '(' || columname || ',' || oldval=
ue || ',' || newvalue ||')' from changedinfo);
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 perform insert_info(v_message);
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 raise notice '%',v_message;
> =C2=A0 =C2=A0 END;
> $$ LANGUAGE plpgsql;


You don't need a temp table for that. You can create the array directly fr=
om the new and old records:

    v_message :=3D array[concat_ws(',', 'empName', old.empname, new.empnam=
e), 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.