Postgres Pro
Downloads
Home   >   mailing lists

Re: How to set array element to null value - Mailing list pgsql-general

From Pavel Stehule
Subject Re: How to set array element to null value
Date
Msg-id CAFj8pRAJCyJF0VAuwW1V6sDgi0mY+c61_n-SWf=GkaYtYuk=9Q@mail.gmail.com
Whole thread Raw
In response to How to set array element to null value  (Brahmam Eswar <brahmam1234@gmail.com>)
List pgsql-general
Tree view


2018-07-09 11:58 GMT+02:00 Brahmam Eswar <brahmam1234@gmail.com>:
I'm trying to reset array element to null. but 3rd line of below snippet is giving the compilation error.


FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP
IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN
X[indx_1].REFERENCE_VALUE:='';
END IF;
END LOOP;


a) plpgsql doesn't support complex expressions on left side of assign command, b) '' is not NULL in PostgreSQL

you can write your code some like

DECLARE r RECORD;
BEGIN
  FOR i IN array_lower(x, 1) .. array_upper(x, 1)
  LOOP
    r := x[i];
    IF r.reference_value = 'ABC' THEN
      r.reference_value := NULL;
      x[i] := r;
    END IF;
  END LOOP;
END;

Regards

Pavel


 
--
Thanks & Regards,
Brahmeswara Rao J.

pgsql-general by date:

Previous
From: Brahmam Eswar
Date:
Subject: How to set array element to null value
Next
From: Thomas Kellerer
Date:
Subject: Re: How to set array element to null value