CALL

An SQL command to invoke a procedure

CALL is an SQL command to invoke a procedure.

CALL was added in PostgreSQL 11 (commit e4128ee7).

Examples

Using following tables:

postgres=# CREATE TABLE foo (id serial primary key, val INT);
CREATE TABLE
postgres=# CREATE TABLE bar (id serial primary key, foo_id INT, bar_val INT);
CREATE TABLE

a simple procedure to insert some data:

postgres=# CREATE OR REPLACE PROCEDURE insert_data(foo_val INT, bar_val INT)
              LANGUAGE SQL
            AS $$
              WITH foo AS (
                INSERT INTO foo VALUES (DEFAULT, foo_val) RETURNING id
              )
              INSERT INTO bar VALUES (DEFAULT, (SELECT foo.id FROM foo), bar_val);
            $$;
CREATE PROCEDURE
postgres=# CALL insert_data(1, 2);
CALL

A procedure which does the same but returns data (but note that in contrast to a function this output cannot be combined with another query):

postgres=# CREATE OR REPLACE PROCEDURE insert_data_ret(INOUT foo_val INT, IN bar_val INT)
              LANGUAGE SQL
            AS $$
              WITH foo AS (
                INSERT INTO foo VALUES (DEFAULT, foo_val) RETURNING id
              )
              INSERT INTO bar VALUES (DEFAULT, (SELECT foo.id FROM foo), bar_val)
                RETURNING id;
            $$;


postgres=# CALL insert_data_ret(1, 2);
 foo_val 
---------
       2
(1 row)
  • PostgreSQL documentation: CALL

Categories

SQL command

See also

procedure