How to assign variable in array value inside function proc.

Поиск
Список
Период
Сортировка
От aditya desai
Тема How to assign variable in array value inside function proc.
Дата
Msg-id CAN0SRDEAEkZ6jBMVjGJGa-2dBKJejXt7Gq0BCwnY7fdsO0w8CA@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to assign variable in array value inside function proc.
Список 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.



В списке pgsql-sql по дате отправления: