CODEWITHSUNDEEP
postgresqlplpgsql
zoran
zoran

Reputation: 81

C-like compound assignment operator in Postgresql

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?

Upvotes: 1

Views: 140

Answers (2)

Belayer
Belayer

Reputation: 14861

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.

Upvotes: 1

zoran
zoran

Reputation: 81

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.

Upvotes: 1

Related Questions