How to assign variable in array value inside function proc.

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.

Responses

Browse pgsql-sql by date

  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