Subject: Re: Out of memory error
To: Pgsql Performance <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>
 <4af2ff21-1140-ccbd-5adf-926dc816782f@gmx.net>
 <CAN0SRDHZD4AD99AxDoaTEGg=S2cYHjOj0v0jFWSdMbXfbhr3CQ@mail.gmail.com>
 <CAHOFxGouFSFZTvWMD=jb8ZFk9f-mmZAtUGJmwAMhZPO6OqTJMg@mail.gmail.com>
 <CAN0SRDHoBqVK13rSr=A4f+AzzAD8SrT0L2z5m6-oh4Tau9U9pQ@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <f8952c15-6cde-2a0a-b095-cfedd34ba734@gmx.net>
Date: Wed, 24 Nov 2021 13:12:14 +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: 
 <CAN0SRDHoBqVK13rSr=A4f+AzzAD8SrT0L2z5m6-oh4Tau9U9pQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/f8952c15-6cde-2a0a-b095-cfedd34ba734%40gmx.net>
Precedence: bulk

aditya desai schrieb am 24.11.2021 um 08:31:
> H Michael,
> Please see insert_info function below. Also r_log_message is composite d=
ata type and it's definition is also given below.
>
> CREATE OR REPLACE FUNCTION insert_info(
> =C2=A0 =C2=A0 info_array=C2=A0 r_log_message[]
> ) RETURNS varchar AS $$
> =C2=A0 =C2=A0 DECLARE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 info_element=C2=A0 r_log_message;
> =C2=A0 =C2=A0 BEGIN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 FOREACH info_element IN ARRAY info_array
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 LOOP
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO testaditya(
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 columname,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 oldvalue,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 newvalue
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ) VALUES(
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 info_element.col=
umn_name,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 info_element.old=
value,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 info_element.new=
value
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 );
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 END LOOP;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 RETURN 'OK';
> =C2=A0 =C2=A0 END;
> $$ LANGUAGE plpgsql;

You don't need a loop for that. This can be done more efficiently using un=
nest()


    INSERT INTO testaditya(columname,oldvalue,newvalue)
    select u.*
    from unnest(info_array) as u;