How to do a batch commit in plpgsql?

When I do the below function, some errors occurs:

**ddddl=# select sssss(1,10);
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function sssss(integer,integer) line 8 at SQL statement**

Here is my sample code.

CREATE OR REPLACE FUNCTION sssss(
IN c_1 int,
IN f_i int
) returns void as
$$
    DECLARE t_c     INT;
    BEGIN
        t_c := f_i;
        WHILE c_1 <= t_c
            loop
            IF MOD(c_1, 4) = 1 THEN
                start transaction;
            END IF;
            -- My statements here.
            IF MOD(c_1, 4) = 0 THEN
                COMMIT;
            END IF;
            c_1 := c_1 + 1;
        END loop;
        COMMIT;
    END;
$$ language plpgsql;

So could anyone give me a sample of how to finish my job as the above code?

I’m using PostgreSQL 9.2.

Answer

In general, I think there are three approaches you can take:

• Do without. Just let the whole thing be committed at once, like PostgreSQL wants.
• Write a wrapper in application code that sets up a transaction and calls your function. (This is simple to do in a shell script, for example.)
• Use a hack. One hack that exists is, you can have your function create a database-link to your own database, and perform subtransactions via remote sessions. [More information.] (This is essentially equivalent to the “wrapper in application code” approach, except that the application code is still in PL/pgSQL.)