PgSQL:connect/connectdb (postgres.lsp)

Machine-specific discussion
Unix, Linux, OS X, OS/2, Windows, ..?
Locked
unya
Posts: 27
Joined: Fri Feb 26, 2010 8:30 am
Contact:

PgSQL:connect/connectdb (postgres.lsp)

Post by 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,
postgres.zip
postgres.lsp, postgres.lsp.udiff
(5.5 KiB) Downloaded 364 times

unya
Posts: 27
Joined: Fri Feb 26, 2010 8:30 am
Contact:

More ADDED Re: PgSQL:connect/connectdb (postgres.lsp)

Post by 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 all

;; all cases returns "xx".

(PgSQL:fetch-value 0 1)
(PgSQL:fetch-value 0 (PgSQL:fnumber "value"))
(PgSQL:fetch-value 0 "value") 
thanks.
postgres.zip
postgres.lsp(full), udiff
(6.35 KiB) Downloaded 348 times

Lutz
Posts: 5289
Joined: Thu Sep 26, 2002 4:45 pm
Location: Pasadena, California
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by 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
Posts: 5289
Joined: Thu Sep 26, 2002 4:45 pm
Location: Pasadena, California
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by 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

and also part of:

http://www.newlisp.org/downloads/develo ... nprogress/

unya
Posts: 27
Joined: Fri Feb 26, 2010 8:30 am
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by 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
Posts: 27
Joined: Fri Feb 26, 2010 8:30 am
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by unya »

Hello Lutz,

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

Code: Select all

;; 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,
postgres-newquery.zip
postgres.lsp + postgres.lsp.udiff(ver 2.10)
(6.59 KiB) Downloaded 358 times

Lutz
Posts: 5289
Joined: Thu Sep 26, 2002 4:45 pm
Location: Pasadena, California
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by Lutz »

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

You can replace:

Code: Select all

(dotimes (i nParams) (push ptr-fmt ""))
with shorter, faster:

Code: Select all

(dup ptr-fmt nParams)

unya
Posts: 27
Joined: Fri Feb 26, 2010 8:30 am
Contact:

Re: PgSQL:connect/connectdb (postgres.lsp)

Post by unya »

Thank you Lutz,

I appreciate your advice, grateful for taking the time.

Have a nice weekend.

Locked