PostgreSQL에서 Bind SQL을 수행하는 방법 및 Plan 확인 방법
사용 구문 : PREPARE name [ ( data_type [, ...] ) ] AS statement ; EXECUTE name [ ( data_type [, ...] ) ] ; EXPLAIN EXECUTE name [ ( data_type [, ...] ) ] ; DEALLOCATE name ; 사용 가능 statement : SELECT / INSERT / UPDATE / DELETE / VALUES
|
edb=# deallocate stmt ;
DEALLOCATE
edb=#
edb=# prepare stmt(int , int) as select * from emp where empno =$1 and deptno =$2 ;
PREPARE
edb=# execute stmt( 7934 , 10 ) ;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-------+------+--------------------+---------+------+--------
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(1 row)
edb=# deallocate stmt ;
DEALLOCATE
edb=# prepare stmt(int,varchar) as select * from emp where empno = $1 and job = $2 ;
PREPARE
edb=# execute stmt(7934 , 'CLERK') ;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-------+------+--------------------+---------+------+--------
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(1 row)
edb=# deallocate stmt ;
DEALLOCATE
edb=# prepare stmt(int,varchar) as select * from emp where empno = $1 and job = $2 ;
PREPARE
edb=# explain execute stmt(7934 , 'CLERK') ;
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on emp (cost=0.00..1.21 rows=1 width=45)
Filter: ((empno = 7934::numeric) AND ((job)::text = 'CLERK'::text))
(2 rows)
edb=# deallocate stmt ;
DEALLOCATE