title: Access \set variables in psql for Postgres function bodies
body: <p>I am creating a generic automated Postgres script which includes DDL, DML, functions and triggers. I have achieved to parameterize the schema name with <code>\set</code> in psql in most of the operation but it's not working in case of <strong>function bodies</strong>. I've searched hard and found about the <strong>dynamic SQL</strong> but that also doesn't seem to work.</p>
  
  <p><strong>script.sql</strong></p>
  
  <pre><code>\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();
  </code></pre>
  
  <blockquote>
    <p>psql -p 5432 -f script.sql</p>
  </blockquote>
  
  <p>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?</p>
  
  <p><strong>Execution Logs:</strong></p>
  
  <p><a href="https://i.stack.imgur.com/7xPHb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7xPHb.png" alt="Execution Logs"></a></p>