From: | aditya desai <admad123(at)gmail(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | How to assign variable in array value inside function proc. |
Date: | 2021-10-28 17:43:59 |
Message-ID: | CAN0SRDEAEkZ6jBMVjGJGa-2dBKJejXt7Gq0BCwnY7fdsO0w8CA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-10-28 18:05:43 | Re: How to assign variable in array value inside function proc. |
Previous Message | aditya desai | 2021-10-25 03:27:15 | Re: Convert text to user defined datatype |