Pass column name dynamically from frontend to SQL query

Question

Requirment is we have 10 column in table and in frontend presented as grid of 10 column and a search box with each.

Upon user enter a search in any one column, that column name and value should be passed into where clause.

sample Table

  number    agency        brand   product   ad_product  indication
  1234      salesforce    BMW      sxx       suv          E-class
  5678      apple         iphone   i-14      accessories  B-class

When user search in 'brand' as 'BMW' and ENTER values should be passed like

   select *,'Primary' "type"  from dev.sales where %brand% = %'BMW'%

and When user search in 'agency' as 'apple' and ENTER values should be passed like

   select *,'Primary' "type"  from dev.sales where %agency% = %'apple'%

Stefanov.sm · Accepted Answer · 2022-11-14 15:40:57Z

1

You can do this with a function that uses dynamic SQL. Assuming that your table is called the_table then here it is.

create or replace function dynamic_search(column_name text, search_value text)
returns setof the_table language plpgsql as
$$
declare 
    DYNSQL constant text := 'SELECT * FROM the_table where %I = $1';
begin
    return query execute format(DYNSQL, column_name) using search_value;
end;
$$;

And then query

select *, 'Primary' as "type" 
from dynamic_search('brand', 'BMW');

Please note that this function is SQL injection prone.

edited Nov 14, 2022 at 15:40

answered Nov 14, 2022 at 15:31

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass column name dynamically from frontend to SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related