A Nix-based sandbox to play with the Pagila example database using psql
and ghci
.
The database will be created and initialized the first time we enter nix-shell
:
$ nix-shell
[nix-shell]$
Once within nix-shell
, we can connect using psql
:
[nix-shell]$ psql
psql (14.6)
Type "help" for help.
pagila=#
Or using ghci
and the Rel8
client library:
[nix-shell]$ ghci Rel8Main.hs
ghci> Right conn <- acquire ""
ghci> each actorSchema & limit 3 & select & statement () & flip run conn
Exit nix-shell
, then delete the folders .pg/
and pg_sockets/
:
[nix-shell]$ exit
$ rm -rf .pg/ .pg_sockets/
- Using PostgreSQL in a nix-shell.
- Postgresql/Postgis inside nix-shell with sqitch and default postgres user.
- Nix Recipe: Setup Postgresql.
- Unable to setup postgres in nix-shell.
- trap
explain (verbose true, format json) select actor_id, first_name from actor where actor_id = 1;
prepare foostmt (integer) as select actor_id, first_name from actor where actor_id = $1;
explain (verbose true, format json) execute foostmt(1);
deallocate foostmt;
Interesting that it catches type errors in the prepared statement's parameters:
foodb=# prepare foostmt2 (bytea) as select actor_id, first_name from actor where actor_id = $1;
ERROR: operator does not exist: integer = bytea
LINE 1: ... select actor_id, first_name from actor where actor_id = $1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
-
PREPARE and DEALLOCATE
-
Submits a request to obtain information about the specified prepared statement
On success, a PGresult with status PGRES_COMMAND_OK is returned. The functions PQnparams and PQparamtype can be applied to this PGresult to obtain information about the parameters of the prepared statement, and the functions PQnfields, PQfname, PQftype, etc provide information about the result columns (if any) of the statement.