Break update operation when function returns NULL value in PostgreSQL

Let' assume I have a table named mytable:

Table paragTest

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 :

output table

But what my expectation of output is :

Expected output

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 ?

1 answer

  • answered 2022-05-04 12:13 karthik_ghorpade

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum