Compose string query in postgresql function with NULL argument

Question

I have a postgresql function with this signature:

ver_hijos(
    IN cod character varying,
    IN idpadre integer,
    IN idhijo integer)

Into the body of the function I have the next:

     FOR var_r IN EXECUTE 'SELECT ' || 
    ...........
    ' FROM '||....
     ' WHERE '||....
     ' AND 
        CASE WHEN ' || idpadre || ' IS NULL 
        THEN '||tabla_relacion||'.id_padre IS NULL 
        ELSE '||tabla_relacion||'.id_padre = '||idpadre||'
        END
        AND '||tabla_relacion||'.id_hijo = '||tabla_conceptos||'.id'

Function works fine when idpadre is not null, but if not, the query string concatenate with a null string and it is invalidate, getting the next error:

ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function ver_hijos(character varying,integer,integer) line 10 at FOR over EXECUTE statement

********** Error **********

ERROR: query string argument of EXECUTE is null
SQL state: 22004
Context: PL/pgSQL function ver_hijos(character varying,integer,integer) line 10 at FOR over EXECUTE statement

¿How can I build the function properly for accept NULL values and don't damage the string?

Edit:

If I use format() for build the query:

     FOR var_r IN EXECUTE format('SELECT ' || 
            ...........
            ' FROM '||....
             ' WHERE '||....
             ' AND 
                CASE WHEN ' || idpadre || ' IS NULL 
                THEN '||tabla_relacion||'.id_padre IS NULL 
                ELSE '||tabla_relacion||'.id_padre = '||idpadre||'
                END
                AND '||tabla_relacion||'.id_hijo = |tabla_conceptos||'.id'
                ,idpadre,idpadre)

and I use null argument idpadre=null I get this error:

ERROR:  null values cannot be formatted as an SQL identifier
CONTEXT:  PL/pgSQL function ver_hijos(character varying,integer,integer) line 10 at FOR over EXECUTE statement
********** Error **********

ERROR: null values cannot be formatted as an SQL identifier
SQL state: 22004
Context: PL/pgSQL function ver_hijos(character varying,integer,integer) line 10 at FOR over EXECUTE statement

It's my workaround for resolve it:

DECLARE
........
str_null_case character varying;
........
BEGIN
........
IF idpadre IS NULL THEN
    str_null_case := tabla_relacion||'.id_padre IS NULL';
ELSE
    str_null_case := tabla_relacion||'.id_padre = '||idpadre;
END IF;
........
BODY
.......
' WHERE '||tabla_conceptos||'.id ='|| idhijo ||
 ' AND '||str_null_case||    
 ' AND '||tabla_relacion||'.id_hijo = '||tabla_conceptos||'.id' 
.......

The || operator will yield NULL if any of the expressions is null. So most probably you are passing null in one of the parameters. To generate dynamic SQL format() is a much better choice in Postgres than concatenating with || — user330315
– user330315, Commented Sep 6, 2018 at 8:39
@a_horse_with_no_name, I have edit the question, because if I use format() I have another error — exodehm
– exodehm, Commented Sep 6, 2018 at 9:07
Of course you get an error if that is null. What do you expect to happen? — user330315
– user330315, Commented Sep 6, 2018 at 9:10
Definitively I can t manage a null value into a string :-(. I would like to manage a null argument into the function. I was thinking a workaround like assign a negative value (because i'll never had it out of the function) to null value and then build the string with this condition, but I was wondering if there are best way to do it — exodehm
– exodehm, Commented Sep 6, 2018 at 9:16
But your parameter is not a "value", it's an identifier. You can't have a "null" identifier - which column would you test for is null if none is supplied? — user330315
– user330315, Commented Sep 6, 2018 at 9:17

Laurenz Albe · Accepted Answer · 2018-09-06 11:57:40Z

1

You could solve it like this:

EXECUTE '... CASE WHEN ' || (idpadre IS NULL) || ' THEN ...';

For the ELSE branch, you can use coalesce(idpadre, '').

But it would be much better to write the whole thing as

EXECUTE format('... WHERE %I.id_padre IS NOT DISTINCT FROM $1',
               tabla_relacion)
USING idpadre;

This is simpler and avoids the danger of SQL injection.

edited Sep 6, 2018 at 11:57

answered Sep 6, 2018 at 9:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

exodehm Over a year ago

This resolves perfectly the first part of the string, but not the second one: "ELSE '||tabla_relacion||'.id_padre = '||idpadre||'". Finally, after a few hours trying to advance in this way, I have resolved it using a previous string: str_null_case. I have edited the main because if someone consider it useful, but I'm not happy with my "solution" :-(

Laurenz Albe Over a year ago

I improved the answer.

Collectives™ on Stack Overflow

Compose string query in postgresql function with NULL argument

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related