설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 167건, 최근 0 건
   

Efficient Debugging of Dynamic SQL in Stored Procedures

글쓴이 : 모델광 날짜 : 2024-04-21 (일) 17:21 조회 : 426
When developing stored procedures that incorporate dynamic SQL statements, it is common practice to verify the constructed SQL query stored in a variable. Typically, this is done using the RAISE NOTICE clause to output the query. Once the query is confirmed to be error-free, developers usually switch from RAISE NOTICE to RETURN QUERY EXECUTE to run the query. This standard process, although effective, can often be tedious and error-prone.

To streamline this debugging process, I propose a more practical approach that enhances the ease of debugging without sacrificing the thoroughness of the verification.

Sample Code for Debugging a Procedure with Dynamic SQL

Consider the following PostgreSQL function definition, which is designed to dynamically retrieve employee data based on department and job criteria:

CREATE OR REPLACE FUNCTION p_getemployee_new(
    p_deptid  INT DEFAULT NULL,
    p_job       TEXT DEFAULT NULL,
    p_debug  INT DEFAULT 0
)
RETURNS SETOF emp AS
$$
DECLARE
    v_str TEXT;
BEGIN
    v_str := 'SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
              FROM emp
              WHERE 1 = 1';

    IF p_deptid IS NOT NULL THEN
        v_str := v_str || ' AND deptno = ' || quote_literal(p_deptid);
    END IF;

    IF p_job IS NOT NULL THEN
        v_str := v_str || ' AND job = ' || quote_literal(p_job);
    END IF;

    IF p_debug = 1 THEN
        RAISE NOTICE '%', v_str;
    ELSE
        RETURN QUERY EXECUTE v_str;
    END IF;
END;
$$ LANGUAGE plpgsql;

Key Features of the Approach
1) Dynamic SQL Construction: The function constructs a SQL query based on input parameters for department ID (p_deptid) and job title (p_job). The query construction is dynamic, accommodating variations in the input parameters.

2) Debug Mode Handling: The function includes a p_debug parameter that determines the mode of operation. When p_debug is set to 1, the function does not execute the SQL query. Instead, it outputs the constructed query using RAISE NOTICE, allowing developers to visually inspect the SQL for correctness.

3) Execution Mode: If p_debug is set to 0 (the default value), the function executes the constructed SQL query and returns the result set directly to the caller.

Practical Benefits
This approach significantly simplifies the debugging of dynamic SQL in stored procedures. By toggling a single parameter, developers can switch between viewing the generated SQL and executing it. This method reduces the likelihood of errors during the development phase and speeds up the process of verifying and refining SQL statements.

Sample Output
When you invoke the function with p_debug set to 1, it outputs the generated SQL statement for review:

analdb=# SELECT p_getemployee_new(20, 'CLERK', 1);
NOTICE:  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE 1 = 1 AND deptno = '20' AND job = 'CLERK'


This streamlined debugging process enhances efficiency and ensures that SQL queries embedded within PostgreSQL stored procedures are both accurate and optimized before deployment.

Addendum
Here is the script to create the EMP table and populate it with data:

CREATE TABLE emp (
    empno integer    NOT NULL,
    ename character varying(10),
    job      character varying(9),
    mgr     integer,
    hiredate timestamp without time zone,
    sal         numeric(7,2),
    comm     numeric(7,2),
    deptno   integer
);

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-7-1987', 'dd-mm-yyyy'),1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);


Footnote
This note was created with assistance from ChatGPT.

   

postgresdba.com