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 -- ordinary query])
125 AT_CAPTURE_FILE([get-data.sps])
126 AT_CHECK([cat > get-data.sps <<EOF
128 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
130 /SQL="select * from thing".
137 AT_CHECK([pspp -o pspp.csv get-data.sps])
138 AT_CHECK([cat pspp.csv], [0], [dnl
139 Variable,Description,,Position
142 ,Display Alignment: Right,,
144 bytea,Format: AHEX2,,2
146 ,Display Alignment: Left,,
150 ,Display Alignment: Left,,
154 ,Display Alignment: Right,,
158 ,Display Alignment: Right,,
162 ,Display Alignment: Right,,
164 numeric,Format: E40.6,,7
166 ,Display Alignment: Right,,
170 ,Display Alignment: Left,,
174 ,Display Alignment: Right,,
176 float4,Format: F8.2,,10
178 ,Display Alignment: Right,,
180 float8,Format: F8.2,,11
182 ,Display Alignment: Right,,
184 money,Format: DOLLAR8.2,,12
186 ,Display Alignment: Right,,
188 pbchar,Format: A8,,13
190 ,Display Alignment: Left,,
192 varchar,Format: A8,,14
194 ,Display Alignment: Left,,
196 date,Format: DATE11,,15
198 ,Display Alignment: Right,,
200 time,Format: TIME11.0,,16
202 ,Display Alignment: Right,,
204 timestamp,Format: DATETIME22.0,,17
206 ,Display Alignment: Right,,
208 timestamptz,Format: DATETIME22.0,,18
210 ,Display Alignment: Right,,
212 interval,Format: DTIME13.0,,19
214 ,Display Alignment: Right,,
216 interval_months,Format: F3.0,,20
218 ,Display Alignment: Right,,
220 timetz,Format: TIME11.0,,21
222 ,Display Alignment: Right,,
224 timetz_zone,Format: F8.2,,22
226 ,Display Alignment: Right,,
230 bool,bytea,char,int8,int2,int4,numeric,text,oid,float4,float8,money,pbchar,varchar,date,time,timestamp,timestamptz,interval,interval_months,timetz,timetz_zone
231 .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
232 . ,20,,. ,. ,. ,. ,,. ,. ,. ,. ,,,.,.,.,.,.,.,.,. @&t@
233 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 AT_SETUP([GET DATA /TYPE=PSQL -- empty result set])
239 AT_CAPTURE_FILE([get-data.sps])
240 AT_CHECK([cat > get-data.sps <<EOF
242 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
244 /SQL="select * from empty".
251 AT_CHECK([pspp -o pspp.csv get-data.sps])
252 AT_CHECK([cat pspp.csv], [0], [dnl
253 Variable,Description,,Position
256 ,Display Alignment: Right,,
260 ,Display Alignment: Right,,
264 ,Display Alignment: Right,,
269 AT_SETUP([GET DATA /TYPE=PSQL -- large result set])
271 AT_CAPTURE_FILE([get-data.sps])
272 AT_CHECK([cat > get-data.sps <<EOF
274 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
276 /SQL="select * from large".
279 COMPUTE diff = x - lag (x).
282 SELECT IF (diff <> 1).
296 AT_CHECK([pspp -o pspp.csv get-data.sps])
297 AT_CHECK([cat pspp.csv], [0], [dnl
318 dnl Check for a bug caused by having string variables in the database,
319 dnl all of which are null.
320 AT_SETUP([GET DATA /TYPE=PSQL -- all-null string])
322 AT_DATA([all-null-string.sql],
323 [-- a table which has a text field containing only null, or zero
326 CREATE TABLE foo (int4 int4, text text);
328 INSERT INTO foo VALUES ('12', '');
330 INSERT INTO foo VALUES (null, '');
332 AT_CHECK([psql -h "`pwd`" -p $PG_PORT $PG_DBASE < all-null-string.sql],
334 AT_CAPTURE_FILE([get-data.sps])
335 AT_CHECK([cat > get-data.sps <<EOF
337 /CONNECT="host=$PGHOST port=$PGPORT dbname=$PG_DBASE"
339 /SQL="select * from foo".
346 AT_CHECK([pspp -o pspp.csv get-data.sps])
347 AT_CAPTURE_FILE([pspp.csv])