From: | aditya desai <admad123(at)gmail(dot)com> |
---|---|
To: | Thomas Kellerer <shammat(at)gmx(dot)net> |
Cc: | Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Out of memory error |
Date: | 2021-11-24 07:35:14 |
Message-ID: | CAN0SRDGEJ32wrqizdY_7KVQztOuvmn9+Mbw2_m6LqOU0AOgpOA@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <shammat(at)gmx(dot)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.
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | hpc researcher_mspk | 2021-11-24 12:05:50 | Postgres process count GCC vs Clang is Different on autovaccum=on |
Previous Message | aditya desai | 2021-11-24 07:31:18 | Re: Out of memory error |