PostgreSQL Views with Runtime Parameters

by Horatiu Dan

There aren’t few the 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 post aims to present a way of achieving it 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 build with:

Java 21
Spring Boot version 3.1.15
PostgreSQL Driver version 42.6.0.
Liquibase 4.20.0
Maven 3.6.3

At 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.

create schema pgsetting;

There are three entities – Product, Currency, CurrencyExchange.

@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.

@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 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 straight forward, 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

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

Product

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

CurrencyExchange rates for the 15th of November

+--+----------+----------------+--------------+-----+
|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).

Fetch the products
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.

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:

a view is defined, for the above query – product_view

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 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>

a new entity – ProductView – is defined as part of the domain, together with the corresponding repository

@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;
	
	...
}

@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:

the two parameter values are set as configuration parameters – SELECT set_config(:name, :value, true)
the ProductView entities are fetched using the repository method

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

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.

@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.

@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.

@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.

@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:

+--+-------------------+-----+-----------+----------+
|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.

@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:

+--+-------------------+-----+-----------+----------+
|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.

Resources

PostgreSQL System Admin Functions
The picture was taken at Legoland, Germany

Kaizen Driven Development

pure, tempered, watchful

PostgreSQL Views with Runtime Parameters

Set-Up

Proof of Concept

Creating the Dynamic Report

Sample Code

Resources

Leave a comment Cancel reply

Set-Up

Proof of Concept

Creating the Dynamic Report

Sample Code

Resources

Share this:

Related

Leave a comment Cancel reply