PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres cursor lazy evaluation

I am trying to replicate a function from Oracle that has the following piece of code

OPEN pcursor FOR v_sql;
        loop
            fetch pcursor into
                out_rec.XYZ
            ;

            exit when pcursor%NOTFOUND;

                     INSERT INTO tab1 VALUES (out_rec.XYZ);
                     COMMIT;
            pipe row(out_rec);
        end loop;
EXCEPTION
        WHEN OTHERS THEN
            v_err_code := SQLCODE;

The query stored in v_sql has an issue where for certain specific row values it fails to execute.
I.e. in SQL developer if I execute the query, it returns some data, but if I fetch the resultset to the end or do a count(*) on it, it fails because one of the values in the query returns bad data. The error is irrelevant, but the behavior of this function is that it returns data until it reaches the point of error and then it completes. The data is incomplete (any rows beyond the point of error are not returned) but it is there. This is because of the exception handling clause that doesn’t really do anything (it eats up the exception)

Now when I am trying to replicate this in PostgreSQL, I am facing a couple issues.

  1. I can’t figure out a way to execute the query lazily. When I execute it – it just fails regardless of whether I fetch all data or not.
  2. I created the following code to emulate the cursor’s behavior
CREATE OR REPLACE FUNCTION test_fn()
 RETURNS table (t ret_data_type)
 LANGUAGE plpgsql
AS $function$
...
for t in execute v_sql
    loop
        return next;
    end loop;
    exception when others then
        raise notice 'error';
...

This runs fine for data with no errors in it, but if there is any error in the entire dataset, it just fails and returns no dataset.

My question is: Is there any way to have Postgresql evaluate the cursor lazily (a few rows at a time) and keep returning data until it encounters the error? I am looking around in the docs but I don’t see anything.

Tags:

2

Answers


  1. 0

    It looks like PG fetches all the data before looping over it

    It doesn’t – one of the main points of cursors is not doing exactly that. There isn’t much to add over @Frank Heikens‘ immediate response: put the exception handling inside the loop if you want it to continue.
    demo at db-fiddle

    create table test as select '{"a":"b"}'::text t from generate_series(1,1e5);
insert into test select '{a:1}';--that's not a valid json/jsonb
insert into test select '{"a":"b"}'::text from generate_series(1,1e5);

create or replace function f()returns table(j jsonb) as $f$
declare rec record;
begin 
  for rec in select t from test loop 
  begin j:=rec.t; return next;
  exception when others then raise notice '% ignored',rec.t;
  end;
end loop;
end$f$language plpgsql;

create table test2 as select f();

    NOTICE:  {a:1} ignored
SELECT 200000

    Even though there’s an invalid json in the middle of the input table, handling/ignoring the exception inside the loop lets you continue afterwards.

    I actually tried that but it doesn’t help

    Handling the exception at the end of the whole function will terminate it once you handle or ignore the error. If you moved that inwards, into the loop and added a begin..exception..end wrapping around return next, that still wouldn’t work since the exception’s likely thrown in the assignment above that, in for t in execute v_sql. You’d have to show your actual code to say for sure.

    The when others then is just for demonstration, it’s better to assert and handle different exceptions adequately.

    Login or Signup to reply.
  2. 0

    Based on the great answer of @Zegarek I wrote a small example using assert in an exception block. Never used it before, works really nice, and especially when using the function pg_input_is_valid():

    DO
$$
DECLARE
    _row RECORD;
BEGIN
    FOR _row IN
        SELECT content
        FROM (VALUES 
              (null::text),
              ('{"foo":"bar"}'),
              (''), -- empty string
              ('invalid json'),
              ('{"missing":"value}') -- " missing
             ) s(content)
    LOOP
        BEGIN
            ASSERT pg_input_is_valid(COALESCE(_row.content, '{}'), 'json')
                , 'Content must be valid JSON.';

            RAISE NOTICE '%: Valid JSON: %'
                , clock_timestamp()
                , cast(_row.content AS json);
        EXCEPTION
            WHEN ASSERT_FAILURE THEN
                RAISE NOTICE '%: ASSERTION FAILED - %'
                    , clock_timestamp()
                    , SQLERRM;
            WHEN OTHERS THEN -- catch all
                RAISE NOTICE '%: OTHER EXCEPTION - %'
                    , clock_timestamp()
                    , SQLERRM;
        END;
    END LOOP;
END;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search