How to use variables in procedure (not function)? As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:


declare a variable
select a value into a variable
insert the variable into another table
do this in a transaction
do this in a stored procedure
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.
First of all, 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 plpgsql BEGIN (starting a block) with SQL BEGIN; (starting a transaction). Neither is allowed in an SQL function. The latter is allowed in an SQL procedure.

To use variables, you need LANGUAGE plpgsql (or one of the other PLs). To give you 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.