Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl( id serial PRIMARY KEY, val text UNIQUE NOT NULL);
INSERT INTO tbl VALUES (1, 'foo'), (2, 'bar');

CREATE TABLE

INSERT 0 2

-- PL/pgSQL procedure
CREATE PROCEDURE assign_demo(INOUT _val text DEFAULT null)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   SELECT val FROM tbl WHERE id = 2
   INTO _val;  -- !!!
END
$proc$;

CALL assign_demo();

CREATE PROCEDURE

_val

bar

CALL

-- SQL procedure
CREATE PROCEDURE lang_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql AS
$proc$
SELECT val FROM tbl WHERE id = 2;
$proc$;

CALL lang_sql_demo();

CREATE PROCEDURE

_val

bar

CALL

-- standard SQL procedure
CREATE OR REPLACE PROCEDURE lang_std_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql
BEGIN ATOMIC
SELECT val FROM tbl WHERE id = 2;
END;

CALL lang_std_sql_demo();

CREATE PROCEDURE

_val

bar

CALL