Vertica - Query Returned 0 Rows Where 1 is expected


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.


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    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 $$
    y int;
    y := SELECT COUNT(*) FROM tbl WHERE col1 = 3;
    PERFORM UPDATE tbl SET col2 = 4 WHERE col1 = 3;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file