Обсуждение: How to deal with NULL values on dynamic queries?

Hi,

I have a query that some values could be NULL, how can I deal with this problem?

[code]
  EXECUTE 'INSERT INTO '
        || quote_ident(pNOME_VIEW)
        || '('
        || quote_ident(pCHAVE_1)
        || ', DAT_INICIO, DAT_FIM, COMENT) values ('
        || quote_literal(pVAL_CHAVE_1)
        || ', '
        || quote_literal(pVAL_CHAVE_2)
        || ', '
        || quote_literal(pVAL_CAMPO1)
        || ', '
        || quote_literal(pVAL_COMENT)
        || ')';
[/code]

The variable pVAL_COMENT could be NULL or have a value. How can I deal with this?

Sorry the bad english.

Best Regards,

Hi

Null + 1=null.
and
null + 'a' = null.

I expect that pVAL_COMENT is null (as you say).

===========================
create test environment.
===========================
create table nulltest (col1 varchar(10),col2 varchar(10));
insert into nulltest (col1,col2) values ('A','B');
insert into nulltest (col1) values ('A');
insert into nulltest (col2) values ('B');

select col1,col2 from  nulltest;
select col1||col2 from nulltest;

postgres=# select col1,col2 from  nulltest;
  col1 | col2
------+------
  A    | B
  A    |
       | B
(3 rows)

===========================
null make col1||col2 null
===========================
ex.
postgres=# select col1||col2 from nulltest;
  ?column?
----------
  AB


(3 rows)

=============================================
even if we use quote_literal,we can not avoid tihs behavior.
=============================================
ex.
postgres=# select quote_literal(col1)||quote_literal(col2) from nulltest;
  ?column?
----------
  'A''B'


(3 rows)

================================================
So we can use COALESCE() function to avoid this.
================================================
ex.
postgres=# select COALESCE(col1,'')||COALESCE(col2,'') from  nulltest;
  ?column?
----------
  AB
  A
  B
(3 rows)

ex2.
postgres=# select
quote_literal(COALESCE(col1,''))||quote_literal(COALESCE(col2,'')) from
nulltest;
  ?column?
----------
  'A''B'
  'A'''
  '''B'
(3 rows)


Can you work around  like this?
||quote_literal(COALESCE(pVAL_COMENT,''))

Thank you.

> Hi,
>
> I have a query that some values could be NULL, how can I deal with
> this problem?
>
> [code]
>   EXECUTE 'INSERT INTO '
>         || quote_ident(pNOME_VIEW)
>         || '('
>         || quote_ident(pCHAVE_1)
>         || ', DAT_INICIO, DAT_FIM, COMENT) values ('
>         || quote_literal(pVAL_CHAVE_1)
>         || ', '
>         || quote_literal(pVAL_CHAVE_2)
>         || ', '
>         || quote_literal(pVAL_CAMPO1)
>         || ', '
>         || quote_literal(pVAL_COMENT)
>         || ')';
> [/code]
>
> The variable pVAL_COMENT could be NULL or have a value. How can I deal
> with this?
>
> Sorry the bad english.
>
> Best Regards,
>
>


--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================

Andre Lopes <lopes80andre@gmail.com> writes:
> I have a query that some values could be NULL, how can I deal with this
> problem?

PG 8.4 and up have a function quote_nullable() that would do what I
think you're looking for.  On an older version, you could define such a
function for yourself.

            regards, tom lane