PostgreSQL Views With Runtime Parameters –

by Blog Admin
0 comment
postgresql-views-with-runtime-parameters-–

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:

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

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. 

There are three entities: Product, Currency, and 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 { } @Repository public interface CurrencyRepository extends CrudRepository { } @Repository public interface CurrencyExchangeRepository extends CrudRepository { }

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: 

” data-lang=”application/xml” contenteditable=”false”>

               

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 

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

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

” data-lang=”application/xml” contenteditable=”false”>

                                

  • 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 { List 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 { List 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 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 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 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.

You may also like

Leave a Comment