How to use variables in procedure (not function)?

Question

As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:

1. declare a variable
2. select a value into a variable
3. insert the variable into another table
4. do this in a transaction
5. do this in a stored procedure
6. roll back if necessary

CREATE PROCEDURE test_variable()
LANGUAGE SQL
AS $$
    BEGIN;
        DECLARE some_id INTEGER;
        SELECT nextval('some_sequence') INTO some_id;
        INSERT INTO some_table (some_column) VALUES (some_id);
    END;
$$;

The above is not working out for me. When I search for solutions, there's so many different variables involving functions, $$, declaration, thing not returning anything; can't seem to find a simple example; I just need a clear example of the syntax.

Answer 1

SQL functions or procedures (LANGUAGE sql) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures alike. (Maybe other PLs, too.)

Like dezso commented, do not confuse PL/pgSQL BEGIN (starting a block) with SQL BEGIN; (starting a transaction). Neither is allowed in an SQL function. The latter is also not allowed in an SQL procedure. The manual:

In procedures invoked by the CALL command as well as in anonymous code blocks (DO command), it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/pgSQL.)

To use variables, you need LANGUAGE plpgsql (or one of the other PLs). To give a clear example:

CREATE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   _some_id int;
BEGIN
   SELECT nextval('some_sequence') INTO _some_id;
   INSERT INTO some_table (some_column) VALUES (_some_id);
END
$$;

The exactly same body would work for a function as well.