Search code examples
sqlpostgresqlsubquery

Using FETCH inside a FROM clause in postgres

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?

Solution

  • 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.