1 AT_BANNER([GET DATA /TYPE=PSQL])
4 [AT_SKIP_IF([test "$PSQL_SUPPORT" = no])
13 AT_CHECK([initdb -A trust], [0], [ignore])
14 AT_CHECK([pg_ctl start -w -o "-k `pwd` -h ''"], [0], [ignore])
16 AT_CHECK([createdb -h "`pwd`" -p $PG_PORT $PG_DBASE],
17 [0], [ignore], [ignore])
18 AT_DATA([populate.sql],
19 [CREATE TABLE empty (a int, b date, c numeric(23, 4));
21 -- a largeish table to check big queries work ok.
22 CREATE TABLE large (x int);
23 INSERT INTO large (select * from generate_series(1, 1000));
33 numeric numeric(50,6) ,
44 timestamptz timestamptz ,
49 INSERT INTO thing VALUES (
66 'January 8 04:05:06 1999',
67 'January 8 04:05:06 1999 PST',
72 INSERT INTO thing VALUES (
95 INSERT INTO thing VALUES (
112 '10-Jan-1963 23:58:00',
113 '10-Jan-1963 23:58:00 CET',
114 '2 year 1 month 12 days 1 hours 3 minutes 4 seconds',
118 AT_CHECK([psql -h "`pwd`" -p $PG_PORT $PG_DBASE < populate.sql],
121 m4_define([CLEANUP_PSQL], [pg_ctl stop -W -o "-k `pwd` -h ''"])
123 AT_SETUP([GET DATA /TYPE=PSQL])
126 dnl Test with an ordinary query.
127 AT_CHECK([cat > ordinary-query.sps <<EOF
129 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
131 /SQL="select * from thing".
138 AT_CHECK([pspp -o pspp.csv ordinary-query.sps])
139 AT_CHECK([cat pspp.csv], [0], [dnl
140 Variable,Description,,Position
143 ,Display Alignment: Right,,
145 bytea,Format: AHEX2,,2
147 ,Display Alignment: Left,,
151 ,Display Alignment: Left,,
155 ,Display Alignment: Right,,
159 ,Display Alignment: Right,,
163 ,Display Alignment: Right,,
165 numeric,Format: E40.6,,7
167 ,Display Alignment: Right,,
171 ,Display Alignment: Left,,
175 ,Display Alignment: Right,,
177 float4,Format: F8.2,,10
179 ,Display Alignment: Right,,
181 float8,Format: F8.2,,11
183 ,Display Alignment: Right,,
185 money,Format: DOLLAR8.2,,12
187 ,Display Alignment: Right,,
189 pbchar,Format: A8,,13
191 ,Display Alignment: Left,,
193 varchar,Format: A8,,14
195 ,Display Alignment: Left,,
197 date,Format: DATE11,,15
199 ,Display Alignment: Right,,
201 time,Format: TIME11.0,,16
203 ,Display Alignment: Right,,
205 timestamp,Format: DATETIME22.0,,17
207 ,Display Alignment: Right,,
209 timestamptz,Format: DATETIME22.0,,18
211 ,Display Alignment: Right,,
213 interval,Format: DTIME13.0,,19
215 ,Display Alignment: Right,,
217 interval_months,Format: F3.0,,20
219 ,Display Alignment: Right,,
221 timetz,Format: TIME11.0,,21
223 ,Display Alignment: Right,,
225 timetz_zone,Format: F8.2,,22
227 ,Display Alignment: Right,,
231 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
232 .00,30,a ,.00,.00,.00,-2.560980E+002,this-long-text ,.00,.00,.00,$.01,a ,A ,01-JAN-2000,0:00:00,08-JAN-1999 04:05:06,08-JAN-1999 12:05:06,0 00:01:00,0,10:09:00,4.00
233 . ,20,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
234 1.00,31,b ,1.00,1.00,1.00,6.553500E+004,that-long-text ,.00,1.00,1.00,$1.23,b ,B ,10-JAN-1963,1:05:02,10-JAN-1963 23:58:00,10-JAN-1963 22:58:00,12 01:03:04,25,1:05:02,-7.00
237 dnl Test query with empty result set.
238 AT_CHECK([cat > empty-result.sps <<EOF
240 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
242 /SQL="select * from empty".
249 AT_CHECK([pspp -o pspp.csv empty-result.sps])
250 AT_CHECK([cat pspp.csv], [0], [dnl
251 Variable,Description,,Position
254 ,Display Alignment: Right,,
258 ,Display Alignment: Right,,
262 ,Display Alignment: Right,,
266 dnl Test query with large result set.
267 AT_CHECK([cat > large-result.sps <<EOF
269 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
271 /SQL="select * from large".
274 COMPUTE diff = x - lag (x).
277 SELECT IF (diff <> 1).
291 AT_CHECK([pspp -o pspp.csv large-result.sps])
292 AT_CHECK([cat pspp.csv], [0], [dnl
312 dnl Check for a bug caused by having string variables in the database,
313 dnl all of which are null.
314 AT_DATA([all-null-string.sql],
315 [-- a table which has a text field containing only null, or zero
318 CREATE TABLE foo (int4 int4, text text);
320 INSERT INTO foo VALUES ('12', '');
322 INSERT INTO foo VALUES (null, '');
324 AT_CHECK([psql -h "`pwd`" -p $PG_PORT $PG_DBASE < all-null-string.sql],
326 AT_CAPTURE_FILE([get-data.sps])
327 AT_CHECK([cat > get-data.sps <<EOF
329 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
331 /SQL="select * from foo".
338 AT_CHECK([pspp -o pspp.csv get-data.sps])
339 AT_CAPTURE_FILE([pspp.csv])