Vertica - Query Returned 0 Rows Where 1 is expected

suryateja

Team,

I am using a stored proc and assigning the value to find out number of rows it has updated but it is throwing this error when i am using this.

sample proc i have used is like below -

sq_cmd := 'update table set col1=5 where col2 is null';
result := execute sq_cmd;
raise notice 'number of records updated:%',result

This is throwing an error like query returned 0 where 1 is expected where as if i run below block then it is running fine and is the query running or getting skipped in the below block.

sq_cmd := 'update table set col1=5 where col2 is null';
execute sq_cmd;

Do let me know how to have an error handling if the sql cmd fails and to find out number of records has been updated as part of this SQL.

Bryan_H

Please see the workaround described at https://docs.vertica.com/12.0.x/en/extending/stored-procedures/
It will be necessary to capture the update row count with a SELECT query to look up record count in advance, for example:
DO $$
DECLARE
y int;
BEGIN
y := SELECT COUNT(*) FROM tbl WHERE col1 = 3;
PERFORM UPDATE tbl SET col2 = 4 WHERE col1 = 3;
END;
$$;