Break update operation when function returns NULL value in PostgreSQL

Question

Let' assume I have a table named mytable:

I have one function which returns text and sometime it can return NULL also. ( this is just demo function in real use case function is complex )

CREATE OR REPLACE FUNCTION parag_test (id text)  
RETURNS text  
LANGUAGE plpgsql  
AS  
$$  
    DECLARE  
    --- variables  
    BEGIN  
    IF(id= 'Ram') THEN
        RETURN 'shyam';
    ELSE 
        RETURN NULL;
    END IF;
    END  
$$  

I want to update mytable till the time when my function returns non NULL values. if it returns NULL value I want to break update operation that time.

if we use below update query it will not stops updating when function returns NULL

update mytable SET id = parag_test (id) ;

Table after triggering above query looks like :

But what my expectation of output is :

because when we try to update second row function parag_test will return NULL and I want to stop update operation there.

So is there any way in PostgreSQL for achieving that ?

Answer 1

If you do have a primary key (say row number) to your table, this approach could work. - https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0350562961be16333f54ebbe0eb5d5cb

CREATE OR REPLACE FUNCTION parag_test()  
RETURNS void  
LANGUAGE plpgsql  
AS  
$$  
    DECLARE
    a int;
    i varchar;
    BEGIN  
    FOR a, i IN SELECT row_num, id FROM yourtable order by row_num asc
    LOOP
        IF(i = 'Ram') THEN
            UPDATE yourtable set id = 'shyam' where row_num = a;
        END IF;
        IF (i is null) then
            EXIT;
        END IF;
    END LOOP;
    END;
$$