Postgres Pro
Downloads
Home   >   mailing lists

Re: How to assign variable in array value inside function proc. - Mailing list pgsql-sql

From aditya desai
Subject Re: How to assign variable in array value inside function proc.
Date
Msg-id CAN0SRDGkmSyqM0YrGRJbWEi3cyJ46p5xvDCv69Jqds5iUQ0fGw@mail.gmail.com
Whole thread Raw
In response to Re: How to assign variable in array value inside function proc.  (aditya desai <admad123@gmail.com>)
Responses Re: How to assign variable in array value inside function proc.  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
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 by date:

Previous
From: aditya desai
Date:
Subject: Re: How to assign variable in array value inside function proc.
Next
From: "David G. Johnston"
Date:
Subject: Re: How to assign variable in array value inside function proc.