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 ?

What is the ordering column of the table? "First", "second" .. is meaningless regarding an Sql table until ORDER BY is provided. — Serg, May 04 '22 at 11:06
What should be the purpose of this function? Why not simply use an update command instead? — Jonas Metzler, May 04 '22 at 11:07
@JonasMetzler this is just a demo function . In my case I have complex one so I want that if function returns NULL then update operation stops. — Parag Jain, May 04 '22 at 11:08
@a_horse_with_no_name I am using 14.1 . corrected the tag. thanks — Parag Jain, May 04 '22 at 11:14
The function knows nothing about the rows being processed. It only sees the input parameter. To "stop" processing you need some kind of WHERE clause in your UPDATE statement. The only transforms one value into another it has nothing to do with the evaluation and processing of the rows changed by the UPDATE statement — , May 04 '22 at 11:16

karthik_ghorpade · Answer 1 · 2022-05-04T12:19:02.743

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;
$$

Break update operation when function returns NULL value in PostgreSQL

1 Answers1