Postgres: Find previous and next row or existing value created by SERIAL colum for separate query

Question

I got a Postgres 13 DB and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps.

In each row binary data is stored too and I would like to process it row after row moving backward or forward.

My idea would be to do SELECT id FROM tbl; and then use this as a lookup table. Another idea would be to create a view with a new continuous sequence and to get the max value and then iterate between sequence start and max value.

Example:

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
  id SERIAL PRIMARY KEY, 
  name VARCHAR, 
  data BYTEA
);

INSERT INTO tbl (name, data) VALUES
  ('a', '0xff')
, ('b', '0xff')
, ('v', '0xff')
, ('w', '0xff')
, ('t', '0xff')
, ('u', '0xff')
;

DELETE FROM tbl WHERE id IN (3, 5);

As you can see the row id is having gaps. I would like to do e.g. something like this (pseudocode):

std::vector<int> vec = SELECT id FROM tbl; -- -> Store values in C++ vector named vec

for(int i=0; i<vec.size(); i++) { -- Loop through vector resp. every row in the database table
    SELECT * FROM tbl WHERE id=vec[i] -- Query every row in tbl
    //Do something with the binary data -- Process its data
}

Is there a better way to achieve this?

If it plays a role, I am using libpq.

Answer 1

Seeing the loop in pseudo-code, it seems like you want a SQL cursor. it's instantiated with DECLARE associated to a query with an ORDER BY if needed, and you can use MOVE to move forward or backward, and FETCH to fetch the current row, or several rows, and finally CLOSE when you're done.

All these are SQL statements and independent from any client-side language.