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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Alexander Kleinhans
  • 401
  • 2
  • 7
  • 15
  • 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? –  Nov 02 '18 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". – Alexander Kleinhans Nov 02 '18 at 12:43
  • 1
    As documented in the manual the declare section must come before the first begin block –  Nov 02 '18 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. – András Váczi Nov 02 '18 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. – Erwin Brandstetter Nov 02 '18 at 20:21
  • @dezso: All true - in plpgsql. The OP has LANGUAGE SQL ... – Erwin Brandstetter Nov 02 '18 at 20:38
  • @ErwinBrandstetter you are right, but the body of the procedure suggests otherwise. In an SQL procedure you cannot declare a variable (other than the attributes) anyway, just as you hinted in your answer. – András Váczi Nov 03 '18 at 13:55

1 Answers1

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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 04 '23 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): https://dba.stackexchange.com/a/290944/3684, https://dba.stackexchange.com/a/118419/3684 – Erwin Brandstetter Aug 04 '23 at 22:33