PostgreSQL Views With Runtime Parameters

gowebing

There are many situations when applications are requested to be agile and versatile enough so that they can run dynamic reports for which the input comes at runtime.

This article aims to present a way of achieving as much by leveraging the temporary configuration parameters supported by PostgreSQL databases.

According to the PostgreSQL documentation, starting with version 7.3, it is possible to set a configuration parameter using the set_config(name, value, is_local) function. Later, the value of the previously set parameter may be read using the current_setting(name) function, converted if needed and used. If the third parameter of the former function is true, the changed setting will only apply to the current transaction.

This is exactly what is needed here — a way of providing a runtime parameter value that can be used as part of an atomic operation.

Set Up

The sample application is built with:

At the application level, the Maven project is configured to use Spring Data JPA and Liquibase dependencies.

The domain is represented by products, whose prices are in various currencies. For converting between currencies, a currency exchange rate exists. The goal is to be able to read all products with their prices represented in a certain currency, at the rate of a certain day.

Proof of Concept

In order to start modelling, one shall first create a new schema, once connected to the database. 

PLSQL
  
create schema pgsetting;

There are three entities: Product, Currency, and CurrencyExchange. 

Java
  
@Entity
@Table(name = "product")
public class Product {
 
    @Id
    @Column(name = "id")
    private Long id;
 
    @Column(name = "name", nullable = false)
    private String name;
 
    @Column(name = "price", nullable = false)
    private Double price;
 
    @ManyToOne
    @JoinColumn(name = "currency_id")
    private Currency currency;
     
    ...
}
 
@Entity
@Table(name = "currency")
public class Currency {
 
    @Id
    @Column(name = "id", nullable = false)
    private Long id;
 
    @Column(name = "name", nullable = false)
    private String name;
 
    ...
}
 
 
@Entity
@Table(name = "currency_exchange")
public class CurrencyExchange {
 
    @Id
    @Column(name = "id", nullable = false)
    private Long id;
 
    @Column(name = "date", nullable = false)
    private LocalDate date;
 
    @ManyToOne
    @JoinColumn(name = "from_currency_id", nullable = false)
    private Currency from;
 
    @ManyToOne
    @JoinColumn(name = "to_currency_id", nullable = false)
    private Currency to;
 
    @Column(name = "value", nullable = false)
    private Double value;
 
    ...
}

Each one has a corresponding CrudRepository. 

Java
  
@Repository
public interface ProductRepository extends CrudRepository<Product, Long> { }
 
@Repository
public interface CurrencyRepository extends CrudRepository<Currency, Long> { }
 
@Repository
public interface CurrencyExchangeRepository extends CrudRepository<CurrencyExchange, Long> { }

The data source is configured as usual in the application.properties file, together with the path to the Liquibase changelog file that records a few simple change sets for initializing the schema with the three tables and the relations among them.

For details, the application properties and db/changelog/schema-init.xml files may be explored.

The root changelog file is: 

XML
  
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <include file="/db/changelog/schema-init.xml"/>
     
</databaseChangeLog>

When the application is started, the change sets are executed in the order they are declared. So far, everything is straightforward, nothing out of the ordinary — a simple Spring Boot application whose database changes are managed with Liquibase. 

Creating the Dynamic Report

Let’s assume that currently the application has two currencies defined — RON and EUR and two products with their prices recorded in different currencies. 

Currency 

Plain Text
  
+--+----+
|id|name|
+--+----+
|1 |RON |
|2 |EUR |
+--+----+

Product 

Plain Text
  
+--+-------------------+-----+-----------+
|id|name               |price|currency_id|
+--+-------------------+-----+-----------+
|1 |Swatch Moonlight v1|100  |2          |
|2 |Winter Sky         |1000 |1          |
+--+-------------------+-----+-----------+

CurrencyExchange Rates for the 15th of November 

Plain Text
  
+--+----------+----------------+--------------+-----+
|id|date      |from_currency_id|to_currency_id|value|
+--+----------+----------------+--------------+-----+
|1 |2023-11-15|2               |1             |5    |
|2 |2023-11-15|2               |2             |1    |
|3 |2023-11-15|1               |2             |0.2  |
|4 |2023-11-15|1               |1             |1    |
+--+----------+----------------+--------------+-----+

The aimed result is a product report with all prices in EUR, using the exchange rate from the 15th of November 2023. This means the price of the second product needs to be converted.

To ease the design, the previously set goal is divided into smaller parts, then conquered. Conceptually, products shall be fetched and their prices converted (if needed).

  1. Fetch the products.
  2. Convert the prices in the requested currency, using the exchange rate of the requested day.

The former is trivial. A Spring Data Repository method would easily allow getting the products — List<Product> findAll().

The latter is achievable through a query that makes the conversions.

PLSQL
  
SELECT p.id,
       p.name,
       p.price * e.value price,       
       e.to_currency_id currency_id,
       e.date
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
        e.to_currency_id = 2 and
        e.date = '2023-11-15'

In order to unite the two, the following are accomplished:

It is defined in the product-view.sql file and added as an idempotent operation in a repeatable Liquibase change set that is run whenever changed.

XML
  
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
 
    <include file="/db/changelog/schema-init.xml"/>
 
    <changeSet id="repeatable" author="horatiucd" runOnChange="true">
        <sqlFile dbms="postgresql" path="db/changelog/product-view.sql"/>
    </changeSet>
 
</databaseChangeLog>
Java
  
@Entity
@Immutable
public class ProductView {
 
    @Id
    private Long id;
 
    private String name;
 
    private Double price;
 
    private LocalDate date;
 
    @ManyToOne
    @JoinColumn(name = "currency_id")
    private Currency currency;
     
    ...
}
Java
  
@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {
 
    List<ProductView> findAll();
}

The application is now able to construct the desired report, but only for a hardcoded currency and exchange rate.

In order to pass the two at runtime, the following are performed in the same transaction:

Also, the product_view is modified to read the configuration parameters set as part of the current transaction and select the data accordingly.

PLSQL
  
SELECT p.id,
       p.name,
       p.price * e.value price,
       e.date,
       e.to_currency_id currency_id
FROM product p
LEFT JOIN currency_exchange e on p.currency_id = e.from_currency_id and
        e.to_currency_id = current_setting('pgsetting.CurrencyId')::int and
        e.date = current_setting('pgsetting.CurrencyDate')::date;

current_setting('pgsetting.CurrencyId') and current_setting('pgsetting.CurrencyDate') calls read the previously set parameters, which are further converted and used.

The implementation needs some additional adjustments.

ProductViewRepository is enhanced with a method that allows setting the configuration parameters.

Java
  
@Repository
public interface ProductViewRepository extends org.springframework.data.repository.Repository<ProductView, Long> {
 
    List<ProductView> findAll();
 
    @Query(value = "SELECT set_config(:name, :value, true)")
    void setConfigParam(String name, String value);
}

The last parameter is always set to true, thus the value is kept only during the current transaction.

Also, a ProductService is defined to clearly mark all operations involved in the transaction.

Java
  
@Service
public class ProductService {
 
    private final ProductViewRepository productViewRepository;
 
    public ProductService(ProductViewRepository productViewRepository) {
        this.productViewRepository = productViewRepository;
    }
 
    @Transactional
    public List<ProductView> getProducts(Currency currency, LocalDate date) {
        productViewRepository.setConfigParam("pgsetting.CurrencyId",
                String.valueOf(currency.getId()));
 
        productViewRepository.setConfigParam("pgsetting.CurrencyDate",
                DateTimeFormatter.ofPattern("yyyy-MM-dd").format(date));
 
        return productViewRepository.findAll();
    }
}

The name of the parameters are the ones used in the product_view definition.

To certify the implementation, two tests are set up.

Java
  
@SpringBootTest
class Product1Test {
 
    @Autowired
    private CurrencyRepository currencyRepository;
 
    @Autowired
    private ProductRepository productRepository;
 
    @Autowired
    private CurrencyExchangeRepository rateRepository;
 
    @Autowired
    private ProductService productService;
 
    private Currency ron, eur;
    private Product watch, painting;
    private CurrencyExchange eurToRon, ronToEur;
    private LocalDate date;
 
    @BeforeEach
    public void setup() {
        ron = new Currency(1L, "RON");
        eur = new Currency(2L, "EUR");
        currencyRepository.saveAll(List.of(ron, eur));
 
        watch = new Product(1L, "Swatch Moonlight v1", 100.0d, eur);
        painting = new Product(2L, "Winter Sky", 1000.0d, ron);
        productRepository.saveAll(List.of(watch, painting));
 
        date = LocalDate.now();
        eurToRon = new CurrencyExchange(1L, date, eur, ron, 5.0d);
        CurrencyExchange eurToEur = new CurrencyExchange(2L, date, eur, eur, 1.0d);
        ronToEur = new CurrencyExchange(3L, date, ron, eur, .2d);
        CurrencyExchange ronToRon = new CurrencyExchange(4L, date, ron, ron, 1.0d);
        rateRepository.saveAll(List.of(eurToRon, eurToEur, ronToEur, ronToRon));
    }
}

The former fetches the products with prices in EUR, using the recorded exchange rates. 

Java
  
@Test
void prices_in_eur() {
    List<ProductView> products = productService.getProducts(eur, date);
    Assertions.assertEquals(2, products.size());
 
    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getCurrency().getId().equals(eur.getId())));
 
    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getDate().equals(date)));
 
    Assertions.assertEquals(watch.getPrice(),
            products.get(0).getPrice());
    Assertions.assertEquals(painting.getPrice() * ronToEur.getValue(),
            products.get(1).getPrice());
}

When called, product_view is: 

Plain Text
  
+--+-------------------+-----+-----------+----------+
|id|name               |price|currency_id|date      |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|100  |2          |2023-11-15|
|2 |Winter Sky         |200  |2          |2023-11-15|
+--+-------------------+-----+-----------+----------+

The latter fetches the products with prices in RON, using the same exchange rates. 

Java
  
@Test
void prices_in_ron() {
    List<ProductView> products = productService.getProducts(ron, date);
    Assertions.assertEquals(2, products.size());
 
    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getCurrency().getId().equals(ron.getId())));
 
    Assertions.assertTrue(products.stream()
            .allMatch(product -> product.getDate().equals(date)));
 
    Assertions.assertEquals(watch.getPrice() * eurToRon.getValue(),
            products.get(0).getPrice());
    Assertions.assertEquals(painting.getPrice(),
            products.get(1).getPrice());
}

When called, product_view is:

Plain Text
  
+--+-------------------+-----+-----------+----------+
|id|name               |price|currency_id|date      |
+--+-------------------+-----+-----------+----------+
|1 |Swatch Moonlight v1|500  |1          |2023-11-15|
|2 |Winter Sky         |1000 |1          |2023-11-15|
+--+-------------------+-----+-----------+----------+

Sample Code

Available here.

 

 

 

 

Top