Re: How to assign variable in array value inside function proc.

Hi ,here is another issue now. If you see the last value in the source
table it considers commas as separate columns and gives errors. Can you
please help?

Function:

CREATE OR REPLACE FUNCTION call_insert_info(
--info_array r_log_message[]
) RETURNS void AS $$
DECLARE
v_message r_log_message[];
OLDVALUE1 varchar(4000);
BEGIN
OLDVALUE1=current_user;

--v_message:='{"(COLUMN1,%OLDVALUE1,NEWVALUE1)","(COLUMN2,OLDVALUE2,NEWVALUE2)","(COLUMN3,OLDVALUE3,NEWVALUE3)","(COLUMN4,OLDVALUE4,NEWVALUE4)"}';
v_message:= array(select '(' || columname || ',' || oldvalue::text || ','
|| newvalue::text ||')' from testaditya2);
perform insert_info(v_message);
raise notice '%',v_message;
END;
$$ LANGUAGE plpgsql;

columname | oldvalue |
newvalue
---------------+---------------------------------------+---------------------------------------
COLUMN1 | %OLDVALUE1 | NEWVALUE1
COLUMN2 | OLDVALUE2 | NEWVALUE2
COLUMN3 | OLDVALUE3 | NEWVALUE3
COLUMN4 | OLDVALUE4 | NEWVALUE4
custom_config | {"page" : 0,"size: : 20 } | {"page" : 1,"size:
: 21 }
custom_config | {"page" : 0,"size": : 23 } | {"page" :
1,"size": : 22 }
custom_config | {"page" : 0,"size": : 23, "time" :1 } | {"page" :
1,"size": : 22,"time" : 1 }

Error:

postgres=# select call_insert_info();
ERROR: malformed record literal: "(custom_config,{"page" : 0,"size": : 23
},{"page" : 1,"size": : 22 })"
DETAIL: Too many columns.
CONTEXT: PL/pgSQL function call_insert_info() line 8 at assignment

Regards,
AD.

On Sat, Oct 30, 2021 at 11:13 PM aditya desai <admad123(at)gmail(dot)com> wrote:

> Thanks David!! This helped.
>
> On Thursday, October 28, 2021, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Thursday, October 28, 2021, aditya desai <admad123(at)gmail(dot)com> wrote:
>>>
>>>
>>> How to assign variable values in v_message in Function 2 and pass to
>>> function 1. How can I do this? Instead of COLUMN1,OLDVALUE1,NEWVALUE1 it
>>> should take variables assigned.
>>>
>>>
>> In pure sql I would do:
>>
>> ARRAY[col1, col2, col3]::type[]
>>
>> To create an array using column references as inputs instead of literals.
>>
>> Likewise, for a composite type:
>>
>> (co1, col2, col3)::type
>>
>> You will probably need to use those constructs as fragments in your
>> pl/pgsql code to build up the array of composites from variables/columns as
>> opposed to trying to write a text literal.
>>
>> If you want to write a literal, though, you may find the format()
>> function to be helpful (and a sight easier to read, and generally safer,
>> than string concatenation, which would be your option of last resort.
>>
>> David J.
>>
>>
>>
>