From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Out of memory error
Date: Wed, 24 Nov 2021 07:31:08 +0100
Message-ID: <4af2ff21-1140-ccbd-5adf-926dc816782f@gmx.net> (raw)
In-Reply-To: <CAN0SRDGZeRyxcB-LWKjNQSi0O3tcK_xKPZdhaLvj_8+sbNPoBA@mail.gmail.com>
References: <CAN0SRDGg3-wH+y7bNvJQa7cdR4wY+7AqoeHbV0urVfBCszSCqg@mail.gmail.com>
<97240.1637733158@sss.pgh.pa.us>
<CAN0SRDGZeRyxcB-LWKjNQSi0O3tcK_xKPZdhaLvj_8+sbNPoBA@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: shammat@gmx.net, pgsql-performance@lists.postgresql.org
Subject: Re: Out of memory error
In-Reply-To: <4af2ff21-1140-ccbd-5adf-926dc816782f@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox