1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
#!/usr/bin/env lem
--
-- This file is part of lem-postgres.
-- Copyright 2011 Emil Renner Berthing
--
-- lem-postgres is free software: you can redistribute it and/or
-- modify it under the terms of the GNU General Public License as
-- published by the Free Software Foundation, either version 3 of
-- the License, or (at your option) any later version.
--
-- lem-postgres is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with lem-postgres. If not, see <http://www.gnu.org/licenses/>.
--
print("Entering " .. arg[0])
local prettyprint
do
local write, format, tostring = io.write, string.format, tostring
function prettyprint(t)
local widths, columns = {}, #t[0]
for i = 1, columns do
widths[i] = 0
end
for i = 0, #t do
local row = t[i]
for j = 1, columns do
local value = row[j]
if value and #value > widths[j] then
widths[j] = #value
end
end
end
for i = 1, #widths do
widths[i] = '%-' .. tostring(widths[i] + 1) .. 's';
end
for i = 0, #t do
local row = t[i]
for j = 1, columns do
write(format(widths[j], row[j] or 'NULL'))
end
write('\n')
end
end
end
local utils = require 'lem.utils'
local postgres = require 'lem.postgres'
local db = assert(postgres.connect([[
host=localhost
user=myuser
password=mypasswd
dbname=mydb
]]))
assert(db:exec(
'CREATE TABLE mytable (id integer PRIMARY KEY, name TEXT)'))
assert(db:exec("COPY mytable FROM STDIN (delimiter ',')"))
assert(db:put('1,alpha\n'))
assert(db:put('2,beta\n'))
assert(db:put('3,gamma\n'))
assert(db:put('4,delta\n'))
assert(db:done())
local res = assert(db:exec('SELECT * FROM mytable WHERE id = $1', '1'))
prettyprint(res)
local res1, res2 = assert(db:exec([[
SELECT count(id) FROM mytable;
SELECT * FROM mytable WHERE id = 2;
]]));
prettyprint(res2)
assert(db:prepare('mystmt', 'SELECT * FROM mytable WHERE id = $1'))
local res = assert(db:run('mystmt', '3'))
prettyprint(res)
assert(db:exec("COPY mytable TO STDIN (format csv)"))
print("\nDumping CSV:")
while true do
local row = assert(db:get())
if type(row) ~= 'string' then break end
io.write(row)
end
assert(db:exec('DROP TABLE mytable'))
print("Exiting " .. arg[0])
-- vim: syntax=lua ts=2 sw=2 noet:
|
