General information about my question and the structure of the Database (db_racao_dev2).

The project is a feed stock application. I’m working with PostgreSQL >14. The database architecture is designed with multiple users (ROLE group called appuser) which each have their own particular schema (user1 -> sch1, user2 -> sch2, etc.). Only the super user of the DATABASE has access to the ‘public’ scheme (therefore it is not public). It has a single table (usuario) where it saves the passwords of the application’s users. Whenever a user is inserted into the usuario table, the criar_usuario_schema() function is called. And then for each schema (to store the data of the respective users) the tables racao, compra_racao, saida_racao e estoque_racao are created. Example:

sch1.racao = are the feed registered in the system;
sch1.compra_racao = are the feed purchased and entered into stock;
sch1.saida_racao = feed that leave stock;
sch1.estoque_racao = current feed present in stock.

Well, I have a function that recalculates the current stock whenever a feed enters or leaves stock. And obviously the triggers that are activated when the feed enters or leaves.

Everything was built dynamically as shown in the code below. However, I don’t know how to put the function that calculates the current stock [atualizar_estoque()] in the criar_usuario_schema() function so that it is also created dynamically.

To facilitate understanding, I provide the database code without schema structure. And other code with the schema structure but without the implementation of what I’m trying to do. It only automates the creation of tables but not the trigger and function to update each estoque_racao of the particular schemas.

Below is a Codes with Minimum reproductive example.

Code that works but without the schema structure. In other words, the stock_ration table is updated whenever feed enters or leaves.

-- Criando Banco de dados 
CREATE DATABASE db_racao_dev2;

-- SET DATABASE to db_racao_dev2
c db_racao_dev2;

-- TABELA racao
CREATE TABLE racao(
    id_racao SERIAL PRIMARY KEY NOT NULL,
    nome VARCHAR(20) NOT NULL,
    proteina REAL NOT NULL
);

-- TABELA compra_racao
CREATE TABLE compra_racao(
    id_comp_racao SERIAL PRIMARY KEY NOT NULL,
    id_racao SERIAL NOT NULL REFERENCES racao(id_racao) ON DELETE CASCADE,
    valor_uni NUMERIC NOT NULL, 
    quantidade REAL NOT NULL, 
    valor_entrada NUMERIC NOT NULL 
);

-- TABELA saida_racao
CREATE TABLE saida_racao(
    id_saida_racao SERIAL PRIMARY KEY NOT NULL,
    id_racao SERIAL NOT NULL REFERENCES racao(id_racao) ON DELETE CASCADE,
    quantidade REAL NOT NULL,
    valor_saida NUMERIC NOT NULL,
    id_comp_racao SERIAL NOT NULL REFERENCES compra_racao(id_comp_racao) ON DELETE CASCADE
    );
    
-- TABELA estoque_racao
CREATE TABLE estoque_racao(
    id_lote SERIAL PRIMARY KEY,
    id_comp_racao INT NOT NULL REFERENCES compra_racao(id_comp_racao) ON DELETE CASCADE,
    id_racao INT NOT NULL REFERENCES racao(id_racao) ON DELETE CASCADE,
    quantidade NUMERIC DEFAULT 0 CHECK (quantidade >= 0),
    valor_total NUMERIC(10, 2) DEFAULT 0 CHECK (valor_total >= 0)
    );
------------------------------------------------------------------------------------------
-- Funçao para atualizar o estoque de raçao sempre que comprar raçao ou sair raçao do estoque
CREATE OR REPLACE FUNCTION atualizar_estoque()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_TABLE_NAME = 'compra_racao' THEN
        IF EXISTS (SELECT 1 FROM estoque_racao WHERE id_racao = NEW.id_racao) THEN
            UPDATE estoque_racao
            SET quantidade = quantidade + NEW.quantidade,
                valor_total = valor_total + NEW.valor_entrada
            WHERE id_racao = NEW.id_racao;
        ELSE
            INSERT INTO estoque_racao (id_comp_racao, id_racao, quantidade, valor_total)
            VALUES (NEW.id_comp_racao, NEW.id_racao, NEW.quantidade, NEW.valor_entrada);
        END IF;
    ELSIF TG_TABLE_NAME = 'saida_racao' THEN
        UPDATE estoque_racao
        SET quantidade = quantidade - NEW.quantidade,
            valor_total = valor_total - NEW.valor_saida
        WHERE id_racao = NEW.id_racao;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
------------------------------------------------------------------------------------------
-- Atribuir o trigger a funçao para a tabela compra racao
CREATE TRIGGER atualizar_estoque_compra_racao
AFTER INSERT ON compra_racao
FOR EACH ROW
EXECUTE FUNCTION atualizar_estoque();

-- Atribuir trigger a funcao atualizar_estoque() ao sair raçao
CREATE TRIGGER atualizar_estoque_saida_racao
AFTER INSERT ON saida_racao
FOR EACH ROW
EXECUTE FUNCTION atualizar_estoque();
------------------------------------------------------------------------------------------
-- Inserindo dados nas tabelas
-- TABELA racao
INSERT INTO racao(nome,proteina)
VALUES
('A',40),
('B',45),
('C',50),
('D',36),
('E',32);

-- TABELA compra_racao
INSERT INTO compra_racao(id_racao,valor_uni,quantidade,valor_entrada)
VALUES
(1,12,100,1200),
(2,13,100,1300),
(3,14,100,1400),
(4,15,100,1500);

-- TABELA saida_racao
INSERT INTO saida_racao(id_racao,quantidade,valor_saida,id_comp_racao)
VALUES
(1,50,600,1),
(2,50,650,2),
(1,10,120,1),
(3,60,840,3);

-- Conferindo quanto tem no estoque
TABLE estoque_racao;

Code that DOESN’T work, but it creates users and schemas dynamically to help implement what I want.

/* Minimal reproductive example.
* Data: 08/05/2024
* Creating app user groups, creating the database, creating schemas and tables for each user
* Author: Carlos Antonio Zarzar */
----------------------------#----------------------------#----------------------------#----------------------------

-----------------------------
-- First let's create a Role Group of app users (appuser)
-----------------------------
-- Role: appuser
-- DROP ROLE IF EXISTS appuser;
CREATE ROLE appuser WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS;
-----------------------------
-- Now we create the database (db_racao_dev2)
-----------------------------
-- Database: db_racao_dev2
-- DROP DATABASE IF EXISTS db_racao_dev2;
CREATE DATABASE db_racao_dev2; 

GRANT TEMPORARY, CONNECT ON DATABASE db_racao_dev2 TO PUBLIC;

-----------------------------
-- We create a users table 
-----------------------------
-- SET DATABASE to db_racao_dev2
c db_racao_dev2
-- DROP TABLE IF EXISTS public.usuarios;
CREATE TABLE usuarios (
    id_usuario SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    senha VARCHAR(100) NOT NULL,
    admin BOOLEAN NOT NULL,
    criado_em TIMESTAMPTZ DEFAULT Now(),
    modificado_em TIMESTAMPTZ NULL,
    CONSTRAINT usuarios_email_key UNIQUE (email)
);

------------------------------------------------------------------------------------------------------------
----------------------------
-- Now let's create a function to automate the creation of schemas, users (role) belonging to the appuser group and their tables (belonging to the dynamically created schema).
-- This function is activated (trigger) whenever a new user is registered in the system.
-- That is, when inserting information into the public.usuario table
-----------------------------
CREATE OR REPLACE FUNCTION criar_usuario_schema()
RETURNS TRIGGER AS $$
DECLARE
    novo_usuario TEXT;
    novo_schema TEXT;
BEGIN
    novo_usuario := 'user' || NEW.id_usuario;
    novo_schema := 'sch' || NEW.id_usuario;

    -- Criar um novo usuário
    EXECUTE format('CREATE ROLE %I WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS', novo_usuario);

    -- Atribuir permissões ao novo usuário
    EXECUTE format('GRANT appuser TO %I', novo_usuario);

    -- Criar um novo esquema para o novo usuário
    EXECUTE format('CREATE SCHEMA %I AUTHORIZATION %I', novo_schema, novo_usuario);

-------------------------# Criando Tabela racao #------------------------------------        
    -- Criar tabela racao no novo esquema
    EXECUTE format(
    'CREATE TABLE %I.racao(
    id_racao SERIAL PRIMARY KEY NOT NULL,
    nome VARCHAR(20) NOT NULL,
    proteina REAL NOT NULL
    );',
    novo_schema
    );
    -- Alterar a propriedade da tabela tipos_carro para o usuário 'novo_usuario' do esquema 'novo_schema'
    EXECUTE format('ALTER TABLE %I.racao OWNER TO %I', novo_schema, novo_usuario);

-------------------------# Criando Tabela compra_racao #---------------------------------        
    -- Criar tabela compra_racao no novo esquema
    EXECUTE format(
    'CREATE TABLE %I.compra_racao(
    id_comp_racao SERIAL PRIMARY KEY NOT NULL,
    id_racao SERIAL NOT NULL REFERENCES %I.racao(id_racao) ON DELETE CASCADE,
    valor_uni NUMERIC NOT NULL, -- Real R$/kg da ração
    quantidade REAL NOT NULL, -- quantidade comprada (kg)
    valor_entrada NUMERIC NOT NULL -- Valor total da compra para essa ração (R$)
    );',
    novo_schema,novo_schema
    );
    -- Alterar a propriedade da tabela tipos_carro para o usuário 'novo_usuario' do esquema 'novo_schema'
    EXECUTE format('ALTER TABLE %I.compra_racao OWNER TO %I', novo_schema, novo_usuario);

-------------------------# Criando Tabela saida_racao #---------------------------------        
    -- Criar tabela saida_racao no novo esquema
    EXECUTE format(
    'CREATE TABLE %I.saida_racao(
    id_saida_racao SERIAL PRIMARY KEY NOT NULL,
    id_racao SERIAL NOT NULL REFERENCES %I.racao(id_racao) ON DELETE CASCADE,
    quantidade REAL NOT NULL,
    valor_saida NUMERIC NOT NULL,
    id_comp_racao SERIAL NOT NULL REFERENCES %I.compra_racao(id_comp_racao) ON DELETE CASCADE
    );',
    novo_schema,novo_schema,novo_schema
    );
    -- Alterar a propriedade da tabela tipos_carro para o usuário 'novo_usuario' do esquema 'novo_schema'
    EXECUTE format('ALTER TABLE %I.saida_racao OWNER TO %I', novo_schema, novo_usuario);

-------------------------# Criando Tabela estoque_racao #---------------------------------        
    -- Criar tabela estoque_racao no novo esquema
    EXECUTE format(
    'CREATE TABLE %I.estoque_racao(
    id_lote SERIAL PRIMARY KEY,
    id_comp_racao INT NOT NULL REFERENCES %I.compra_racao(id_comp_racao) ON DELETE CASCADE,
    id_racao INT NOT NULL REFERENCES %I.racao(id_racao) ON DELETE CASCADE,
    quantidade NUMERIC DEFAULT 0 CHECK (quantidade >= 0),
    valor_total NUMERIC(10, 2) DEFAULT 0 CHECK (valor_total >= 0)
    );',
    novo_schema,novo_schema,novo_schema
    );
    -- Alterar a propriedade da tabela tipos_carro para o usuário 'novo_usuario' do esquema 'novo_schema'
    EXECUTE format('ALTER TABLE %I.estoque_racao OWNER TO %I', novo_schema, novo_usuario);
--------------------------------------------------------------------------------------       
-- I believe that the dynamically created stock update function for the newly created user schema should go in here.

--------------------------------------------------------------------------------------
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-------------------------------------------
CREATE TRIGGER criar_usuario_schema_trigger
AFTER INSERT ON public.usuarios
FOR EACH ROW
EXECUTE FUNCTION criar_usuario_schema();
----------------------------#----------------------------#----------------------------#----------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- Vamos fazer o teste para saber se está tudo funcionando bem
----------------------------------------------------------------
-- Inserindo alguns usuários cadastrados no app (O adm zarzar quem insere os usuários)
INSERT INTO usuarios (nome, email, senha, admin) VALUES ('carlos', '[email protected]', '1234', 'TRUE');
INSERT INTO usuarios (nome, email, senha, admin) VALUES ('rafaela', '[email protected]', '1234', 'TRUE');
INSERT INTO usuarios (nome, email, senha, admin) VALUES ('tateu', '[email protected]', '1234', 'FALSE');
------------------------------------------------------------------------------------------

Note: I placed a comment in the code above where I believe the code should go. “I believe that the dynamically created stock update function for the newly created user schema should go in here.”

I hope to make my question clear to the reader. Please note that I am not an expert in the PostgreSQL programming language. And in advance, I thank you for your attention.