Break update operation when function returns NULL value in PostgreSQL
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 ?
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; $$
do you know?
how many words do you know
