source download postgres.lsp
Module: postgres.lsp
PostgreSQL interface (tested on PostgreSQL 9.4, should work on all supported versions)
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
Version: 2.12 - add MacPorts path, Fix bugs (error), (affected-rows), (fields ...), test against PostgreSQL 9.4
Version: 3.00 - add new postgreSQL imports, use pg_config to find libpq header, improve test coverage
Author: Jeremy Cowgar 2006, Ted Walther 2009, Lutz Mueller 2010, Unya 2012, Neil Tiffin 2015
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: libpq.so
on Mac OS X: libpq.dylib
on Windows: libpq.dll
This library is installed when using the install package . Unix, Linux, and Mac each have package installers that can be used to install the PostgreSQL client (libpq) & server.
Libpq might be in a different location on a particular installation of PostgreSQL or have a different extension. This module attempts to find libpq using pg_config. If you can execute pg_config at the command line and see its results then everything should just work. If not, then you may have to edit the manual search code below to find your specific libpq.
If you are using PostgreSQL from a Linux distribution you will need to install the development headers for libpq, usually called libpq-dev.
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
Connect PgSQL:connect ............. connect to a database PgSQL:close-db ............ close database connection PgSQL:error-conn .......... get connection error message *
Query PgSQL:query ............... execute a SQL statement
Query Results PgSQL:affected-rows ....... number of affected rows from operation PgSQL:data-seek ........... position in result for fetching PgSQL:error ............... get query error message PgSQL:fetch-all ........... get all rows from the last query PgSQL:fetch-row ........... get row from the query result PgSQL:fetch-value ......... get value from the query result PgSQL:fnumber ............. column number of query PgSQL:num-fields .......... columns in result of query PgSQL:num-rows ............ rows in result of query
Info PgSQL:database ............ return all database names PgSQL:fields .............. return all fields in a table PgSQL:tables .............. return all tables names
Misc PgSQL:escape .............. escapes single quote in input string PgSQL:escape-literal ...... escapes literal for PostgreSQL * PgSQL:escape-identifier ... escapes identifier for PostgreSQL *
* API may be specific to PostgreSQL
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)
If the database server is running locally then "localhost" may be used for the host name.
Bugs
This module doesn't support connections through a Unix socket.
Implementation Notes
As of 19 March 2015. On Windows only works with x86 (32bit) PostgreSQL install. pg_config must be in the path. Tested on OSX 10.10.2, Linux SMP Debian 3.16.7-ckt7-1, Windows 8.1 [version 6.3.9600]§
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, client_encoding, options, application_name, fallback_application_name, keepalives, keepalives_idle, keepalives_interval, keepalives_count, tty, sslmode, sslcompression, sslcert, sslkey, sslrootcert, sslcrl, requirepeer, krbsrvname, gsslib, service in PostgreSQL 9.4.
return: true for success or nil on 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 an integer representing one of the following enumerated types: PGRES_EMPTY_QUERY, PGRES_COMMAND_OK, PGRES_TUPLES_OK, PGRES_COPY_OUT, PGRES_COPY_IN, PGRES_BAD_RESPONSE, PGRES_FATAL_ERROR.
The numeric status code can be converted to a string using (PgSQL:result-str status-code). The numeric status code should not be used directly.
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:fname
syntax: (PgSQL:fname int-column)
parameter: int-column - The integer column number.
return: the column name associated with the given column number. 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 query result 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:error-conn
syntax: (PgSQL:error-conn)
return: error message string
Returns the error message most recently generated by an operation on the connection.§
PgSQL:escape
syntax: (PgSQL:escape str-sql)
return: escaped string
This function only escapes 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:escape-literal
syntax: (PgSQL:escape-literal str)
parameter: str - string to be escaped
return: escaped string
This function escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser. escape-literal performs this operation. This function was added in PostgreSQL 9.1 and will throw an error if you are using an older libpq.§
PgSQL:escape-identifier
syntax: (PgSQL:escape-identifier str)
parameter: str - string to be escaped
return: escaped string
This function escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved. This function was added in PostgreSQL 9.1 and will throw an error if you are using an older libpq.§
PgSQL:close-db
syntax: (PgSQL:close-db)
return: Always true.
Closes the database connection and frees associated resources.§
PgSQL:host
syntax: (PgSQL:host)
return: connected host name string
§
PgSQL:port
syntax: (PgSQL:port)
return: connected port name string
§
PgSQL:db
syntax: (PgSQL:db)
return: connected database name string
§
PgSQL:options
syntax: (PgSQL:options)
return: connected options string
§
PgSQL:user
syntax: (PgSQL:user)
return: connected user name string
§
PgSQL:result-str
syntax: (PgSQL:result-str int-status-code)
parameter: int-status-code - An integer query result status code
return: string for the given integer status code
The return value will be one of the following strings "PGRES_EMPTY_QUERY", "PGRES_COMMAND_OK", "PGRES_TUPLES_OK", "PGRES_COPY_OUT", "PGRES_COPY_IN", "PGRES_BAD_RESPONSE", "PGRES_FATAL_ERROR".§
PgSQL:lib-version
syntax: (PgSQL:lib-version)
return: libpq version as string (e.g. "9.1.1")
§
PgSQL:protocol-version
syntax: (PgSQL:protocol-version)
return: protocol version "2", or "3" as string, "0" bad connection.
Interrogates the frontend/backend protocol being used.§
PgSQL:server-version
syntax: (PgSQL:server-version)
return: backend server version as string (e.g. "9.1.1")
- ∂ -
generated with newLISP and newLISPdoc