7

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.

7
  • 1
    Functions can manipulate data just as well - they just can't control transactions. What exactly "isn't working" for you? What is the error you get?
    – user1822
    Nov 2, 2018 at 12:41
  • When I run the above, I can't seem to declare the variable some_id. ERROR: syntax error at or near "INTEGER". Nov 2, 2018 at 12:43
  • 1
    As documented in the manual the declare section must come before the first begin block
    – user1822
    Nov 2, 2018 at 12:44
  • 1
    BEGIN and BEGIN; are two different things. You need at least `DECLARE ...; BEGIN -- without semicolon! ... END; block, just as @a_horse_with_no_name said. Also, a function call is always wrapped in a transaction, so you don't necessarily have to deal with it yourself in a procedure. Nov 2, 2018 at 13:16
  • 1
    procedures are for manipulating data and functions are for reading data. Whoever told you this, may be an expert on flying saucers, but certainly not on Postgres. Nov 2, 2018 at 20:21

1 Answer 1

15

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.

2
  • Your comment about transactions, does a plpgsql procedure use transactions? Is a code block a transaction? Or do you have to use SQL procedures to use transactions?
    – AWEInCA
    Aug 4, 2023 at 17:16
  • 1
    @AWEInCA: Understand the terms "transaction", "code block", "procedure", and "function" to get a grip on this. There are links to the manual in my answer. Most importantly, a PROCEDURE can commit the current transaction and start a new one. Consequently, it cannot be nested in an outer transaction if it does. Related answers (with links to yet more): dba.stackexchange.com/a/290944/3684, dba.stackexchange.com/a/118419/3684 Aug 4, 2023 at 22:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.