Re: How to assign variable in array value inside function proc.

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

 

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