CODEWITHSUNDEEP
postgresql
exodehm
exodehm

Reputation: 581

Compose string query in postgresql function with NULL argument

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' 
.......

Upvotes: 0

Views: 2163

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246163

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.

Upvotes: 1

Related Questions