In postgresql documentation, there is the entry regarding the "CREATE OPERATOR". I'm literally desperate for a compound assignment operator, C or C# like style, in my PL/PGSQL code. Ideally it would work like this

v_my_text  = 'some more text to be added to existing value of v_my_text';

This would of course be the equivalent of

v_my_text := v_my_text || 'some more text...';

I was not able to find any example around the assignment operators. Is something like this possible?

CodePudding user response：

According to feedbacks I was getting, creating = operator is currently not possible. Yet, @Bergi gave me an idea with inout parameters which can simplify my code.

create or replace procedure add_line
(
    p_text_body inout text,
    p_new_text  in text
)
language plpgsql
as $$
begin
    p_text_body := p_text_body || p_next_text || chr(10) || chr(13);
end; $$

It would be used like call add_line(v_output_report, 'New text line here...'); Any better ideas are welcome. See comments on question for more context. Regards.

CodePudding user response：

There is no need for procedure with INOUT parameter (or starting with v14 an OUT parameter). Instead just build an SQL function. (see demo)

create or replace function add_line
                         ( p_text_body in text
                         , p_new_text  in text
                         )
    returns text 
   language sql
as $$
    select concat(p_text_body, E'\n', p_new_text);   
$$;

Notes:

• Use E'\n' (4.1. Lexical Structure: 4.1.2.2. String Constants with C-Style Escapes) rather than chr(10) || chr(13). It adjusts to the proper code for the operating system.
• The normal assignment operator in Postgres is just =. The = is strictly to maintain compatibility with Oracle plsql.
• The concat function is perhaps better than the concatenation operator (||) here as it handles NULLs. concat('A', null) or concat(null, 'A' ) both return 'A' where as 'A' || null or null || 'A' both return null.