CODEWITHSUNDEEP
postgresqlplpgsql
FatFreddy
FatFreddy

Reputation: 1210

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

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)?

Upvotes: 0

Views: 3666

Answers (2)

Ashhar Hasan
Ashhar Hasan

Reputation: 912

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.

Upvotes: 4

user330315
user330315

Reputation:

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);

Upvotes: 3

Related Questions