1

I have a UI where products of various categories will be listed. There will be filters on various attributes which users can select to filter out the products. How should I create a query which will change dynamically based on the selection of filters? Further, once the result set is ready, only few rows to be displayed at a time to the user e.g. 10 rows at a time; value of which again will be decided by the user.

Kartikeya
  • 53
  • 1
  • 1
  • 10
  • Don't use pl/pgsql for that. Use an SQL query builder library for whatever language your web server is written in. – Bergi Jul 22 '20 at 19:48
  • It depends on how dynamic this is supposed to be? Is there a set number of fields that are filter candidates? The row count can be set using ```LIMIT```. For more information see [Dynamic](https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). After reading that and if it looks like that may work, if you have specific questions add to your question above. – Adrian Klaver Jul 22 '20 at 20:13
  • Whatever the implementation, beware of SQL injection with problems like these! – Ruben Helsloot Jul 22 '20 at 20:20

1 Answers1

0

You could write a PL/pgSQL function that takes a JSON with filter fields and values and returns matching rows. But there are libraries for doing that, why not use one of them. What is your UI built on?

On pagination, see Five ways to paginate in Postgres

Vesa Karjalainen
  • 1,087
  • 8
  • 15