PgSQL:connect/connectdb (postgres.lsp)

[unya]

Hello Lutz,



I tried to add a new connectdb fixes and connect: PgSQL of module postgres.lsp.



The reason is that the parameters are omitted and for postgres PQconnectdb is possible, depending on the version parameter is increased.



PgSQLconnect is (C library "libpq"), has been changed in order to allow connections without a password, even if the host name and the setting.



You can be written as follows,

ex1.(PgSQL:connect "" "" "" "mydb");; host (socket file), user (login-name), passwd (empty)

ex2.(PgSQL:connectdb "host=srv port=25432 user=name dbname=mydb") ;; connect other port.



I would appreciate it if you can change it.



Thanks,


[attachment=0]postgres.zip[/attachment]

[unya]

Hi,



add more useful function PgSQL:fnumber added, with extend PgSQL:fetch-value.



PgSQL:fnumber - returns column number.

PgSQL:fetch-value - column number or column name.





"create table test (key integer, value text)"

 key | value

-----+-------

   1 | xx

   2 | yy



(PgSQL:query "SELECT * FROM test")

Code Select Expand


;; all cases returns "xx".

(PgSQL:fetch-value 0 1)
(PgSQL:fetch-value 0 (PgSQL:fnumber "value"))
(PgSQL:fetch-value 0 "value")


thanks.


[attachment=0]postgres.zip[/attachment]

[Lutz]

Thanks for the additions to postgres.sql Unya.



But I do not completely understand the changes to PgSQL:connect.



Does test-pgsql - at the end of the module - still work/connect ?

[Lutz]

Never mind, I think I understand the changes now. The new module is online here:



http://www.newlisp.org/code/modules/postgres.lsp.html">http://www.newlisp.org/code/modules/postgres.lsp.html



and also part of:



http://www.newlisp.org/downloads/development/inprogress/">http://www.newlisp.org/downloads/develo ... nprogress/">http://www.newlisp.org/downloads/development/inprogress/

[unya]

Thanks Lutz,



Thank you for adding, I took a look at the changes.



I think about the query and I wish I could propose Parameterized query function.

[unya]

Hello Lutz,



new PgSQL:query function - extended with parameter, and compatible.

Code Select Expand


;; Example execute
(PgSQL:query "select $1||$2" "abc" "def")
(PgSQL:fetch-all) ; -> (("abcdef"))

(PgSQL:query "select $1 + $2" 10 20)
(PgSQL:fetch-all) ; -> (("30"))

(PgSQL:query "select $1::timestamp + $2::interval" "2012-10-01 00:00:00" "123456 seconds")
(PgSQL:fetch-all) ; -> (("2012-10-02 10:17:36"))

(PgSQL:query "create table tbl (a integer, b integer)")
(dotimes (i 10) (PgSQL:query "insert into tbl values ($1, $2)" i (* i 2)))
;    a | b
;   ---+----
;    0 |  0
;    1 |  2
;    2 |  4
;    ...
;    9 | 18

(PgSQL:query "select * from tbl where a=$1 or a=$2" 2 9)
(PgSQL:fetch-all) ; -> (("2" "4") ("9" "18"))


Thanks,


[attachment=0]postgres-newquery.zip[/attachment]

[Lutz]

Very nice! I am traveling the next two days but will integrate it before the weekend.



You can replace:

Code Select Expand

(dotimes (i nParams) (push ptr-fmt ""))

with shorter, faster:

Code Select Expand

(dup ptr-fmt nParams)

[unya]

Thank you Lutz,



I appreciate your advice, grateful for taking the time.



Have a nice weekend.