source download postgres.lsp
Module: postgres.lsp
PostgreSQL interface (tested on PostgreSQL 8.3)
Version: 1.02 - feature complete
Version: 1.03 - doc formatting
Version: 2.00 - replaced inc with ++
Version: 2.10 - new fnumber and fetch-value
Version: 2.11 - new query with optional parameters
Author: Jeremy Cowgar 2006, Ted Walther 2009, Lutz Mueller 2010, Unya 2012
Requirements
At the beginning of the program file include a load statement for the module:(load "/usr/share/newlisp/modules/postgres.lsp") ; or (module "postgres.lsp") ; loads from (env "NEWLISPDIR") / modules
A version of libpq for a specific platform is required:
on LINUX/UNIX: /usr/local/lib/libpq.so.5.1
on Mac OS X: /usr/local/lib/libpq.dylib
This library is installed when using the Mac OS X install package here
This library might be in a different location on a particular installation of PostgreSQL or have a different name. Change accordingly in the code at the beginning.
The PostgreSQL server itself may reside on a different machine on the network. The library libpq will communicate with that server. The correct connection is created using the PgSQL:connect call.
At the bottom of the module file postgres.lsp a test routine test-pgsql is included to test for correct installation of PostgreSQL. You call it with the same arguments you would pass to :connect
Functions available
PgSQL:connect ............. connect to a database PgSQL:query ............... execute a SQL statement PgSQL:num-rows ............ rows in result of query PgSQL:num-fields .......... columns in result of query PgSQL:fnumber ............. column number of query PgSQL:fetch-value ......... get value from the query result PgSQL:fetch-row ........... get row from the query result PgSQL:fetch-all ........... get all rows from the last query PgSQL:database ............ return all database names PgSQL:tables .............. return all tables names PgSQL:fields .............. return all fields in a table PgSQL:data-seek ........... position in result for fetching PgSQL:error ............... get error message PgSQL:affected-rows ....... number of affected rows from operation PgSQL:escape .............. escapes input string according to the SQL standard PgSQL:close-db ............ close database connection
Differences from the MySQL module
The function :inserted-id isn't supported because PostgreSQL doesn't support it. Instead, use the RETURNING clause in your INSERT statement, then use :fetch-row or :fetch-all to find the value. INSERT RETURNING is a PostreSQL idiom documented here.
There is no :init function because it isn't needed by the underlying library. Just call :connect.
A typical PgSQL session
The following code piece outlines a typical PgSQL session:
Example:The database server is listening on IP 192.168.1.10. The program connects with username "auser" password "secret" to a database with the name "mydb". After connecting SQL statements are performed and finally the program disconnects from the server.(module "postgres.lsp") ; load the module file (PgSQL:connect "192.168.1.10" "auser" "secret" "mydb") ; logon (PgSQL:query "select ...;") ; SQL query (PgSQL:query "insert ...;") ; SQL query ... (PgSQL:close-db)
Bugs
This module doesn't support connections through a Unix socket.§
PgSQL:connect
syntax: (PgSQL:connect str-server str-userID str-password str-db)
parameter: str-server - The host name or IP address or 0 for localhost.
parameter: str-userID - The user ID for authentication.
parameter: str-password - The password for authentication.
parameter: str-db - The name of the database to connect to.
return: true for success or nil for failure.
Connects to a database on server and authenticates a user ID.§
PgSQL:connectdb
syntax: (PgSQL:connectdb str-conninfo)
parameter: str-conninfo - PostgreSQL Connection Parameters, To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = a value. keyword is host, hostaddr, 'port, dbname, user, password, connect_timeout, options, tty, sslmode, requiressl, krbsrvname, gsslib, service in PostgreSQL 8.3.
return: true for success or nil for failure.
Connects to a database on server.§
PgSQL:query
syntax: (PgSQL:query str-sql [param ...])
parameter: str-sql - A valid SQL query string. If parameters are used, they are referred to in the command string as $1, $2, etc.
parameter: param - Specifies the actual values of the parameters.
return: Returns a numeric status code
Sends a SQL query string to the database server for evaluation. The return value will be one of the following: PGRES_EMPTY_QUERY, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PGRES_COPY_OUT, PGRES_COPY_IN, PGRES_BAD_RESPONSE, PGRES_FATAL_ERROR
From the libpq documentation:If the result status is PGRES_TUPLES_OK, then the functions described below can be used to retrieve the rows returned by the query. Note that a SELECT command that happens to retrieve zero rows still shows PGRES_TUPLES_OK. PGRES_COMMAND_OK is for commands that can never return rows (INSERT, UPDATE, etc.). A response of PGRES_EMPTY_QUERY might indicate a bug in the client software.
Example:(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"))§
PgSQL:num-rows
syntax: (PgSQL:num-rows)
return: Number of rows from last query.
§
PgSQL:num-fields
syntax: (PgSQL:num-fields)
return: Number of columns from last query.
§
PgSQL:fnumber
syntax: (PgSQL:fnumber str-column)
parameter: str-column - The column name.
return: the column number associated with the given column name. return nil when not found column name.
§
PgSQL:fetch-value
syntax: (PgSQL:fetch-value num-row col)
parameter: num-row - row number
parameter: col - column number or column name string.
return: A single value
Fetches the single value in the row and column specified. Used by the :fetch-row and :fetch-all functions. A field containing the NULL value will return the symbol NULL
§
PgSQL:fetch-row
syntax: (PgSQL:fetch-row)
return: A list of field elements.
Fetches a row from a previous SQL PgSQL:query select statement. Subsequent calls fetch row by row from the result table until the end of the table is reached.§
PgSQL:fetch-all
syntax: (PgSQL:fetch-all)
return: All rows/fields from the last query, or nil
The whole result set from the query is returned at once as a list of row lists.§
PgSQL:databases
syntax: (PgSQL:databases)
return: A list of databases.
Performs the query SELECT datname FROM pg_database which shows all the database schemas hosted by the connected server.§
PgSQL:tables
syntax: (PgSQL:tables)
return: A list of tables in the database, or nil
Performs the query SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'§
PgSQL:fields
syntax: (PgSQL:fields str-table)
parameter: str-table - The name of the table.
return: A list of fields in the table, or nil
Unlike the equivalent function in the MySQL module, this function only shows the names of all the fields in the given table. It does not show the field specification, which you would need to recreate the table.§
PgSQL:data-seek
syntax: (PgSQL:data-seek num-offset)
parameter: num-offset - The '0' based offset to position inside the data set.
return: Always true.
Sets a position in the result set which will be used by the next PgSQL:fetch-row call. If the offset is out of the allowed range for the result set a subsequent fetch-row will return nil.§
PgSQL:error
syntax: (PgSQL:error)
return: A string containing the error message.
If there was no error, this function returns nil.§
PgSQL:affected-rows
syntax: (PgSQL:affected-rows)
return: Number of rows affected by the last PgSQL:query operation, or nil
This function will only return a value following the execution of an INSERT, UPDATE, DELETE, MOVE, FETCH, or COPY statement, or an EXECUTE of a prepared query that contains an INSERT, UPDATE, or DELETE statement. It will return nil after all other queries.§
PgSQL:escape
syntax: (PgSQL:escape str-sql)
return: escaped string
This function will escape the ' character in str-sql, as per the SQL standard. Depending on whether you are using binary data or have configured Postgres to allow C escapes you may need more advanced escaping than this function provides.§
PgSQL:close-db
syntax: (PgSQL:close-db)
return: Always true.
Closes the database connection and frees associated resources.- ∂ -
generated with newLISP and newLISPdoc