How to concat a string with null in a postgres function with plpgsql?

Question

I want to write a function, witch selects some values from atable and then insert them into btable.

DECLARE
loRecord record;
loRTest record;
lsQueryExecute text;

BEGIN

SELECT INTO loRTest 
    1 as test1,
    0 as test2,
    NULL::int as test_fk1,
    NULL::timestamp as test_fk2,
    NOW() as teststamp,
    true as test_bool
FROM atable

raise notice 'result  % ', loRTest;

/* result (1,0,,,"2020-01-29 11:28:33.785621+01",t) */

doing some stuff with the values and finaly inserting them into another table:

lsQueryExecute = 
'insert into btable (test1, test2, test_fk1, test_fk2, teststamp, test_bool) 
VALUES(
''' || loRTest.test1 || ''',
''' || loRTest.test2 || ''',
''' || loRTest.test_fk1 || ''',
''' || loRTest.test_fk2 || ''',
''' || loRTest.teststamp || ''',
''' || loRTest.test_bool ||''')';

/* EXECUTE */
 raise notice 'query  % ', lsQueryExecute; 

/*  query  <NULL>  */

well, concatenation with || and a null sets the whole string to null. Whats the correct (easy, nice ) way to concat the string (postgres 10)?

Use CONCAT function instead of ||. It ignores NULL values (>9.6). If you are on <9.6, use COALESCE(NULL, ''). — Ashhar Hasan
– Ashhar Hasan, Commented Jan 29, 2020 at 10:45
There is a problem with possible sql injection, so don't do this without using quote_literal or quote_nullable functions, or use USING clause — Pavel Stehule
– Pavel Stehule, Commented Jan 30, 2020 at 8:25

Ashhar Hasan · Accepted Answer · 2020-01-31 20:38:21Z

4

Please avoid using Dynamic SQL from user input to prevent SQL injection. DO NOT USE BELOW IF INPUTS ARE UNTRUSTED.

In PostgreSQL, the || operator concatenates STRINGs but with a NULL argument the result is NULL.

In PostgreSQL >= 9.1

Use the CONCAT/CONCAT_WS (With Separator) function instead of ||. It will ignore NULL values.

SELECT CONCAT('foo ', NULL, 'bar'); returns foo bar.

If you want a more natural result, use CONCAT_WS. SELECT CONCAT_WS(' ', 'foo', NULL, 'bar'); returns foo bar. (Notice the removed trailing space)

In PostgreSQL < 9.1:

You can combine COALESCE with the || operator like so:

SELECT COALESCE('foo', '') || COALESCE(NULL, '') || COALESCE('bar', ''); returns foobar. You can adjust white-space according to your needs.

edited Jan 31, 2020 at 20:38

answered Jan 29, 2020 at 10:50

Ashhar Hasan

96215 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pavel Stehule Over a year ago

Attention - when is string used for dynamic SQL, then any variant of concat function is dangerous (due SQL injection).

Ashhar Hasan Over a year ago

Thanks @PavelStehule. Added a big black warning about it.

Pavel Stehule Over a year ago

@AshharHasan - you should to expect so any text type input is untrusted. The vector of attach should be very different

Ashhar Hasan Over a year ago

@PavelStehule Absolutely agree. My answer would still be ok if the question was just about CONCAT instead of Dynamic SQL.

score 3 · Accepted Answer · 2020-01-29 11:00:01Z

3

Don't concatenate the values, use proper placeholders

lsQueryExecute := 
  'insert into btable (test1, test2, test_fk1, test_fk2, teststamp, test_bool) 
   VALUES ($1, $2, $3, $4, $5, $6)';

execute lsQueryExecute 
  using loRTest.test1, loRTest.test2, loRTest.test_fk1, 
        loRTest.test_fk2, loRTest.teststamp, loRTest.test_bool;

If you need to generate dynamic SQL, the best way to do that is to use the format() function.

But why the dynamic SQL to begin with? You can do that with a single INSERT statement:

insert into btable
SELECT 1 as test1,
       0 as test2,
       NULL::int as test_fk1,
       NULL::timestamp as test_fk2,
       NOW() as teststamp,
       true as test_bool
FROM atable

If you can't use an INSERT...SELECT you still don't need dynamic SQL in your PL/pgSQL loop:

insert into btable (test1, test2, test_fk1, test_fk2, teststamp, test_bool) 
VALUES (loRTest.test1, loRTest.test2, loRTest.test_fk1, 
        loRTest.test_fk2, loRTest.teststamp, loRTest.test_bool);

edited Jan 29, 2020 at 11:00

answered Jan 29, 2020 at 10:49

user330315

3 Comments

FatFreddy Over a year ago

thx, thats what im looking for. The dynamic SQL is for some manipulation and selecting some other records with the test_fks. i´ll try

user330315 Over a year ago

@FatFreddy: that "manipulation" and selecting other rows could be done with expressions in the SELECT and joining to other tables. Don't do inefficient and slow single row inserts when you can do it with a bulk operation. But if you don't need dynamic column or table names, you don't need dynamic SQL

FatFreddy Over a year ago

finished the task with your help, now its time for optimization:-)

Collectives™ on Stack Overflow

How to concat a string with null in a postgres function with plpgsql?

2 Answers 2

Please avoid using Dynamic SQL from user input to prevent SQL injection. DO NOT USE BELOW IF INPUTS ARE UNTRUSTED.

In PostgreSQL >= 9.1

In PostgreSQL < 9.1:

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Please avoid using Dynamic SQL from user input to prevent SQL injection. DO NOT USE BELOW IF INPUTS ARE UNTRUSTED.

In PostgreSQL >= 9.1

In PostgreSQL < 9.1:

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related