postgresqlplpgsqlpsqlstored-functionscitus

Access \set variables in psql for Postgres function bodies

I am creating a generic automated Postgres script which includes DDL, DML, functions and triggers. I have achieved to parameterize the schema name with \set in psql in most of the operation but it's not working in case of function bodies. I've searched hard and found about the dynamic SQL but that also doesn't seem to work.

script.sql

\set schema_name myschema

CREATE SCHEMA IF NOT EXISTS :schema_name;

CREATE table :schema_name.employee (
 id serial,
 name text ,
 dob timestamp,
 leaves_count int,
 CONSTRAINT employee_pk PRIMARY KEY (id)
);

SELECT create_distributed_table(concat(:'schema_name', '.employee'), 'id');


CREATE table :schema_name.leaves (
 id serial,
 reason text,
 ts timestamp,
 employee_id int,
 CONSTRAINT leaves_pk PRIMARY KEY (id)
);

-- STORED PROCEDURE
CREATE OR REPLACE
FUNCTION :schema_name.deduct_leave_count() RETURNS TRIGGER AS $BODY$ BEGIN
UPDATE
    :schema_name.employee s
SET
    leaves_count = s.leaves_count - 1
WHERE
    s.id = NEW.employee_id;
RETURN NEW;
END $BODY$ LANGUAGE plpgsql;

-- TRIGGER
CREATE TRIGGER trigger_deduct_leave_count BEFORE
INSERT
    ON
    :schema_name.leaves FOR EACH ROW EXECUTE PROCEDURE :schema_name.deduct_leave_count();

psql -p 5432 -f script.sql

I am getting error in function as it can't read the variable set globally. Is there any way to access the global variable or -v flag of psql inside functions?

Execution Logs:

Execution Logs

Solution

  • The body of a function is a quoted literal. Dollar-quoted, but all the same. The manual:

    Variable interpolation will not be performed within quoted SQL literals and identifiers.

    There are various ways around this. Since you are running a script in psql, I suggest you let Postgres do the concatenation for you with format() and execute with \gexec:

    -- TRIGGER FUNCTION
SELECT format($$
CREATE OR REPLACE FUNCTION %1$I.deduct_leave_count()
  RETURNS trigger LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE %1$I.employee s
   SET    leaves_count = s.leaves_count - 1
   WHERE  s.id = NEW.employee_id;

   RETURN NEW;
END
$func$
$$, :'schema_name')\gexec

    Detailed explanation:

    Aside 1: A (trigger) function is not a procedure. Related:

    Aside 2: Since a schema name is an identifier, you may want to use double-quotes. psql supports that:

    CREATE table :"schema_name".leaves

    Preserves the string as given (incl. capitalization) and defends against SQL injection.

    format() with %I is a bit smarter and only adds double quotes where they make a difference. This avoids noise in the function definition. See: