Re: How to set array element to null value

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2018-07-09 11:58 GMT+02:00 Brahmam Eswar <brahmam1234@xxxxxxxxx>:

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.