Using FETCH inside a FROM clause in postgres

Question

So I had a statement:

SELECT jsonb_build_object(
            'type',       'Feature',
            'geometry',   ST_AsGeoJSON(line)::jsonb,
            'properties', to_jsonb(inputs) - 'line'
        ) as feature from (select line, line_types from table_a) inputs;

which worked fine except the select statement inside the FROM clause was returning too many rows. So I want to just FETCH to just do x rows at a time. I declared a cursor on select line, line_types from table_a and when I did FETCH FORWARD 5 FROM mycur; it returned the first 5 rows. Yet when I try to replace the select with fetch, it won't let me:

SELECT jsonb_build_object(
            'type',       'Feature',
            'geometry',   ST_AsGeoJSON(line)::jsonb,
            'properties', to_jsonb(inputs) - 'line'
        ) from (FETCH 1 FROM mycur) inputs;
ERROR:  syntax error at or near "FETCH"
LINE 5:         ) from (FETCH 1 FROM mycur) inputs;
                        ^

i assume you can't use fetch in a subquery? I tried

SELECT jsonb_build_object(
            'type',       'Feature',
            'geometry',   ST_AsGeoJSON(line)::jsonb,
            'properties', to_jsonb(inputs) - 'line'
        ) as feature from (select FETCH 1 FROM mycur) inputs;
ERROR:  syntax error at or near "1"
LINE 5:         ) as feature from (select FETCH 1 FROM mycur) inputs...
                                                ^

I do see that you can use just the FETCH statement in SELECT but thats not really what I want like:

SELECT jsonb_build_object(
            'type',       'Feature',
            'geometry',   ST_AsGeoJSON(line)::jsonb,
            'properties', to_jsonb(inputs) - 'line'
        ) as feature from (select line, line_type from table_a fetch first 10 rows only) inputs;

is there now way to use FETCH FROM Cursor as a subquery?

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-02-07T05:19:30

No, using FETCH in a subquery is not possible.

You should look into using keyset pagination:

Let's assume that the primary key of table_a is id.

Then you start with

SELECT jsonb_build_object(...) AS feature,
       id
FROM (SELECT line, line_type
      FROM table_a
      ORDER BY id
      FETCH FIRST 10 ROWS ONLY) AS inputs;

similar to what you have in mind.

Now remember the highest id returned from that query; I'll call it last_id in the following.

Fetch the next ten rows with

SELECT jsonb_build_object(...) AS feature,
       id
FROM (SELECT line, line_type
      FROM table_a
      WHERE id > last_id
      ORDER BY id
      FETCH FIRST 10 ROWS ONLY) AS inputs;

This can be repeated to fetch more pages of 10 rows as needed.

The beauty of the method is that it uses the primary key index for high efficiency.

Using FETCH inside a FROM clause in postgres

1 Answers