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.