On Sep 22, 2005, at 11:16 AM, Андрей wrote:
The reason is to get parameters description (if there are any in
query) before binding them to the backend. For example 'select *
from table_name where column_name = $1'. I would like to get
required type of parameter $1 to bind later. But I can't be sure at
prepare time, that I'll need such description in future.
It has been a while since I looked at this, but I think what I just
said will give you that (Parse -> Describe -> Sync).
If you are familiar with Lisp, I have included some code below which
might help you. The xp-send function adds a sync message and flushes
the output.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
; parse a statement and return parameter and row info
(defun xp-prepare (connection sql &key (name "") parameter-types)
(declare (type string sql)
(type string name))
(let ((stream (ref connection))
(result nil)
(row-info nil)
(param-info nil)
(err nil)
(param-count (length parameter-types)))
;; parse
(write-byte +parse+ stream)
(write-int32 (+ +int32-length+ (length name) 1 (length sql) 1
+int16-length+ (* +int32-length+ param-count)) stream)
(write-cstring name stream)
(write-cstring sql stream)
(write-int16 param-count stream)
(loop for pt in parameter-types do (write-int32 pt stream))
;; describe the statement
(write-byte +describe+ stream)
(write-int32 (+ +int32-length+ 1 (length name) 1) stream)
(write-byte #.(char-code #\S) stream)
(write-cstring name stream)
(xp-send stream) ;sync
(loop for op = (read-byte stream) do
(case op
(#.+parse-complete+
(read-empty-response stream)
(setf result t))
(#.+parameter-description+
(setf param-info (read-parameter-description stream)))
(#.+row-description+
(setf row-info (read-row-description stream)))
(#.+no-data+ ;not a select statment, get this instead of
row-description
(read-empty-response stream))
(#.+ready-for-query+
(ready-for-query connection)
(return t))
(t
(setf err (common-op-handler connection op)))))
(values result param-info row-info err)))
PGLISP 4 > (xp-prepare *connection* "select * from test where ab = $1")
T
#(25)
#(#<COLSPEC ab (18098 1 25) 100DF68B> #<COLSPEC ac (18098 2 25)
100DF617> #<COLSPEC coolstuff (18098 3 25) 100DF59B> #<COLSPEC vcar
(18098 4 1043) 100DF563>)
NIL
=== psql 3 ===
Table "public.test"
Column | Type | Modifiers
-----------+-----------------------+-----------
ab | text |
ac | text |
coolstuff | text |
vcar | character varying(20) |
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
