Обсуждение: How to assign variable in array value inside function proc.
Hi,
I have a user defined type, table, and two functions as shown below.
Type:
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) | | |
Table:
postgres=# \d testaditya;
Table "public.testaditya"
Column | Type | Collation | Nullable | Default
-----------+-------------------------+-----------+----------+---------
columname | character varying(30) | | |
oldvalue | character varying(4000) | | |
newvalue | character varying(4000) | | |
Function 1:
CREATE OR REPLACE FUNCTION insert_info(
info_array r_log_message[]
) RETURNS varchar AS $$
DECLARE
info_element r_log_message;
BEGIN
FOREACH info_element IN ARRAY info_array
LOOP
INSERT INTO testaditya(
columname,
oldvalue,
newvalue
) VALUES(
info_element.column_name,
info_element.oldvalue,
info_element.newvalue
);
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql;
Function 2:
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)"}';
perform insert_info(v_message);
raise notice '%',v_message;
END;
$$ LANGUAGE plpgsql;
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.
e.g.
username=current_user;
v_message:='{"(COLUMN1,username,NEWVALUE1)","(COLUMN2,OLDVALUE2,NEWVALUE2)","(COLUMN3,OLDVALUE3,NEWVALUE3)","(COLUMN4,OLDVALUE4,NEWVALUE4)"}';
It is considering as a text. Function 2 should generate an ARRAY and pass to function 1.
Please help.
Regards,
Aditya.
On Thursday, October 28, 2021, aditya desai <admad123@gmail.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.
Thanks David!! This helped.
On Thursday, October 28, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, October 28, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, October 28, 2021, aditya desai <admad123@gmail.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)::typeYou 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.
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@gmail.com> wrote:
Thanks David!! This helped.
On Thursday, October 28, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:On Thursday, October 28, 2021, aditya desai <admad123@gmail.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)::typeYou 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.
On Mon, Nov 1, 2021 at 9:20 AM aditya desai <admad123@gmail.com> wrote:
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?v_message:= array(select '(' || columname || ',' || oldvalue::text || ',' || newvalue::text ||')' from testaditya2);
Please don't top-post. Also, take the time to remove anything not necessary to provide useful context.
The only help I can give is to reiterate that trying to do string concatenation and writing out text literals for arrays and composites is something that should just be avoided. If you cannot, learn the quoting and escaping rules for the thing you are trying to build and obey them.
David J.