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

Postgresql – Why is returning 1 required in this Postgres code block?

Asking this question for a colleague who ran into this behaviour with PostgreSQL. Hoping somebody out there can solve this puzzle for us.

The following logs Rows updated: 3 when run:

drop table if exists test;
create table test (str text);

insert into test(str)
values ('row1'), ('row2'), ('row3');

do $$
declare
    _row_count bigint = -1;
begin

    prepare update_test as
        update test
        set str = str
        where true
        returning 1; -- why is this needed?

    execute 'execute update_test';
    get diagnostics _row_count := row_count;

    deallocate update_test;

    raise notice 'Rows updated: %', _row_count;
end
$$ language plpgsql;

However, the returning 1 line should not be necessary, as we’re looking at the rows count diagnostic. Indeed, running the contents of the anonymous code block directly logs 3 even with the returning 1 removed.

But running the whole thing with the returning 1 removed logs a value of zero, not the expected 3.

Update:

The example above is a simplication of the problem. What we’re actually trying to achieve here is a general purpose procedure that can run a variety of named prepared statements. As such, the dynamic call is necessary.

Here is a bigger example of what we’re trying to achieve:

-- general purpose procedure
drop procedure if exists run_sql(text);
create procedure run_sql(named_statement text)
as $$
declare
    _row_count bigint = -1;
begin
    -- do some stuff

    execute 'execute ' || named_statement;
    get diagnostics _row_count := row_count;

    raise notice 'Rows updated: %', _row_count;

    -- do some other stuff
end
$$ language plpgsql;

-- example usage
prepare update_test as
        update test
        set str = str
        where true
        returning 1; -- why is this needed?
call run_sql('update_test');
deallocate update_test;

So we cannot simplify the code in the procedure to call execute directly: it has to be a dynamic call. But it would be good if the passed in statements did not have to have returning 1 on the end.

Tags:

2

Answers


  1. 0

    To follow up on my comment. execute 'execute ... is redundant so simplify to:

    do $$
declare
    _row_count bigint = -1;
begin

    execute  'update test
        set str = str
        where true';

    get diagnostics _row_count := row_count;


    raise notice 'Rows updated: %', _row_count;
end
$$ language plpgsql;

NOTICE:  Rows updated: 3
DO
    Login or Signup to reply.
  2. 0

    The construction is rather strange as well: The plpgsql statement EXECUTE doesn’t cache the query plan. And because of this you use PREPARE to create a query plan that can be repeated. Caching the query plan is the only thing that could have some value in here, anything to avoid SQL injection can be done by placeholders in plpgsql or format() in combination with plpgsql’s EXECUTE.

    And if you would use a plain UPDATE in your code, the plan would have been cached by default. Using less code and simpler code would result in the same functionality: Secure and fast code.

    DO
$$
    DECLARE
        _row_count BIGINT;
    BEGIN
        UPDATE test
        SET str = str
        WHERE TRUE;

        GET DIAGNOSTICS _row_count := ROW_COUNT;

        RAISE NOTICE 'Rows updated: %', _row_count;
    END
$$ LANGUAGE plpgsql;

    Rows updated: 3

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search