This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on the Dynamic SQL Query of PostgreSQL.
Many times custom filters and sorting requires for selecting customized data.
Generally, what we are doing is, we call a stored procedure using master filters and display results on grid or report, but user requires a more custom filter on returned data.
You can also say like the user needs particular column filtering. Now, this column filter called as custom filtering and which we can achieve by dynamic SQL.
The user can also demand custom sorting, for this, we have to construct SQL using custom sorting column.
Dynamic SQL means, you can prepare or build your SQL statement basis on different parameters or results.
Below is a demonstration of PostgreSQL dynamic query using a custom filter.
First create one sample Employee table using 10 test records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE TABLE Employee ( EmpID SERIAL ,EmpName CHARACTER VARYING(50) ,Gender CHAR(1) ,AGE SMALLINT ); INSERT INTO Employee ( EmpName ,Gender ,AGE ) VALUES ('Anvesh','M',27) ,('Mohan','M',30) ,('Roy','M',31) ,('Meera','F',27) ,('Richa','F',26) ,('Martin','M',35) ,('Mahesh','M',38) ,('Paresh','M',22) ,('Alina','F',21) ,('Alex','M',24); |
Below is a sample stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
CREATE OR REPLACE FUNCTION fn_GetEmployeeDataByCustomFilter ( Paging_PageSize INTEGER = NULL ,Paging_PageNumber INTEGER = NULL ,Paging_ColumnFilterExpression TEXT = NULL ,Paging_SortExpression TEXT = NULL ) RETURNS TABLE ( EmpID INTEGER ,EmpName CHARACTER VARYING ,Gender CHAR(1) ,Age SMALLINT ) AS $BODY$ DECLARE PageNumber BIGINT; SQL TEXT; Paging_PageFilterExpression VARCHAR; BEGIN /**************************************************************** Construct Where expression for Custom Paging **************************************************************** */ IF Paging_ColumnFilterExpression IS NULL THEN Paging_ColumnFilterExpression := ' '; ELSE Paging_ColumnFilterExpression := ' AND ' || Paging_ColumnFilterExpression; END IF; /**************************************************************** Construct Sort expression for Custom Paging **************************************************************** */ IF paging_sortexpression IS NULL THEN paging_sortexpression := 'EmpName'; END IF; /**************************************************************** Construct Custom paging parameter... **************************************************************** */ IF (paging_pagesize IS NOT NULL AND paging_pagenumber IS NOT NULL) THEN PageNumber := (Paging_PageSize * (Paging_PageNumber-1)); END IF; /* *************************************************************** Custome paging SQL Query construction....... **************************************************************** */ SQL := ' WITH cteCustomFilter AS ( SELECT EmpID ,EmpName ,Gender ,Age FROM public.Employee WHERE 1=1 '||Paging_ColumnFilterExpression||' ORDER BY '||paging_sortexpression||' ) SELECT EmpID ,EmpName ,Gender ,Age FROM cteCustomFilter '; IF (paging_pagesize IS NOT NULL AND paging_pagenumber IS NOT NULL) THEN SQL:= SQL || 'LIMIT '||Paging_PageSize||' OFFSET '||PageNumber; END IF; RETURN QUERY EXECUTE SQL; EXCEPTION WHEN OTHERS THEN RAISE; END; $BODY$ LANGUAGE 'plpgsql'; |
Show some results:
|
1 |
SELECT *FROM public.fn_GetEmployeeDataByCustomFilter(3,1,'empname=''Mahesh''',NULL) |
In the above call, I filtered only “Mahesh” so it returned only one record.
The application is responsible to create this custom parameter for a custom filter. You just need to append filter or sorting string with main SQL Query.
This stored procedure has four parameters first two for custom paging and last two for custom filter and sorting.
Please visit this URL: to understand custom paging.
PostgreSQL Custom Paging using LIMIT and OFFSET
Now, about the dynamic query in which I am passing filter column name through input parameter.
This filter and sorting parameter are required to concat with the main SQL query for customized results.
input parameter: Paging_ColumnFilterExpression is for custom filter and Paging_SortExpression is for custom sorting.
You should also set some default value for this kind of variables or parameters.
Now examine second sample:
|
1 |
SELECT *FROM public.fn_GetEmployeeDataByCustomFilter(5,1,'empname like ''%M%''','age desc') |
You can see both filters and sort in this result.
We have sort age column by descending and select employee starting with ‘M’.
