~ubuntu-branches/ubuntu/natty/postgresql-8.4/natty-updates

SELECT * FROM ts_parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">

/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234

<i <b> wow < jqw <> qwerty');

SELECT to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">

/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234

<i <b> wow < jqw <> qwerty');

100

SELECT length(to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">

101

/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234

102

<i <b> wow < jqw <> qwerty'));

103

104

-- ts_debug

105

106

107

108

-- to_tsquery

109

110

SELECT to_tsquery('english', 'qwe & sKies ');

111

SELECT to_tsquery('simple', 'qwe & sKies ');

112

SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC ');

113

SELECT to_tsquery('english', 'asd&(and|fghj)');

114

SELECT to_tsquery('english', '(asd&and)|fghj');

115

SELECT to_tsquery('english', '(asd&!and)|fghj');

116

SELECT to_tsquery('english', '(the|and&(i&1))&fghj');

117

118

SELECT plainto_tsquery('english', 'the and z 1))& fghj');

119

SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');

120

SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg');

121

SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg');

122

SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg';

123

124

SELECT ts_rank_cd(to_tsvector('english', '

125

Day after day, day after day,

126

We stuck, nor breath nor motion,

127

As idle as a painted Ship

128

Upon a painted Ocean.

129

Water, water, every where

130

And all the boards did shrink;

131

Water, water, every where,

132

Nor any drop to drink.

133

S. T. Coleridge (1772-1834)

134

'), to_tsquery('english', 'paint&water'));

135

136

SELECT ts_rank_cd(to_tsvector('english', '

137

Day after day, day after day,

138

We stuck, nor breath nor motion,

139

As idle as a painted Ship

140

Upon a painted Ocean.

141

Water, water, every where

142

And all the boards did shrink;

143

Water, water, every where,

144

Nor any drop to drink.

145

S. T. Coleridge (1772-1834)

146

'), to_tsquery('english', 'breath&motion&water'));

147

148

SELECT ts_rank_cd(to_tsvector('english', '

149

Day after day, day after day,

150

We stuck, nor breath nor motion,

151

As idle as a painted Ship

152

Upon a painted Ocean.

153

Water, water, every where

154

And all the boards did shrink;

155

Water, water, every where,

156

Nor any drop to drink.

157

S. T. Coleridge (1772-1834)

158

'), to_tsquery('english', 'ocean'));

159

160

--headline tests

161

SELECT ts_headline('english', '

162

Day after day, day after day,

163

We stuck, nor breath nor motion,

164

As idle as a painted Ship

165

Upon a painted Ocean.

166

Water, water, every where

167

And all the boards did shrink;

168

Water, water, every where,

169

Nor any drop to drink.

170

S. T. Coleridge (1772-1834)

171

', to_tsquery('english', 'paint&water'));

172

173

SELECT ts_headline('english', '

174

Day after day, day after day,

175

We stuck, nor breath nor motion,

176

As idle as a painted Ship

177

Upon a painted Ocean.

178

Water, water, every where

179

And all the boards did shrink;

180

Water, water, every where,

181

Nor any drop to drink.

182

S. T. Coleridge (1772-1834)

183

', to_tsquery('english', 'breath&motion&water'));

184

185

SELECT ts_headline('english', '

186

Day after day, day after day,

187

We stuck, nor breath nor motion,

188

As idle as a painted Ship

189

Upon a painted Ocean.

190

Water, water, every where

191

And all the boards did shrink;

192

Water, water, every where,

193

Nor any drop to drink.

194

S. T. Coleridge (1772-1834)

195

', to_tsquery('english', 'ocean'));

196

197

SELECT ts_headline('english', '

198

<html>

199

200

<body>

201

Sea view wow <u>foo bar</u> <i>qq</i>

202

203

ff-bg

204

205

document.write(15);

206

</script>

207

</body>

208

</html>',

209

to_tsquery('english', 'sea&foo'), 'HighlightAll=true');

210

211

--Check if headline fragments work

212

SELECT ts_headline('english', '

213

Day after day, day after day,

214

We stuck, nor breath nor motion,

215

As idle as a painted Ship

216

Upon a painted Ocean.

217

Water, water, every where

218

And all the boards did shrink;

219

Water, water, every where,

220

Nor any drop to drink.

221

S. T. Coleridge (1772-1834)

222

', to_tsquery('english', 'ocean'), 'MaxFragments=1');

223

224

--Check if more than one fragments are displayed

225

SELECT ts_headline('english', '

226

Day after day, day after day,

227

We stuck, nor breath nor motion,

228

As idle as a painted Ship

229

Upon a painted Ocean.

230

Water, water, every where

231

And all the boards did shrink;

232

Water, water, every where,

233

Nor any drop to drink.

234

S. T. Coleridge (1772-1834)

235

', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2');

236

237

--Fragments when there all query words are not in the document

238

SELECT ts_headline('english', '

239

Day after day, day after day,

240

We stuck, nor breath nor motion,

241

As idle as a painted Ship

242

Upon a painted Ocean.

243

Water, water, every where

244

And all the boards did shrink;

245

Water, water, every where,

246

Nor any drop to drink.

247

S. T. Coleridge (1772-1834)

248

', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1');

249

250

--FragmentDelimiter option

251

SELECT ts_headline('english', '

252

Day after day, day after day,

253

We stuck, nor breath nor motion,

254

As idle as a painted Ship

255

Upon a painted Ocean.

256

Water, water, every where

257

And all the boards did shrink;

258

Water, water, every where,

259

Nor any drop to drink.

260

S. T. Coleridge (1772-1834)

261

', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***');

262

263

--Rewrite sub system

264

265

CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);

266

\set ECHO none

267

\copy test_tsquery from stdin

268

'New York' new & york | big & apple | nyc

269

Moscow moskva | moscow

270

'Sanct Peter' Peterburg | peter | 'Sanct Peterburg'

271

'foo bar qq' foo & (bar | qq) & city

272

273

\set ECHO all

274

275

ALTER TABLE test_tsquery ADD COLUMN keyword tsquery;

276

UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);

277

ALTER TABLE test_tsquery ADD COLUMN sample tsquery;

278

UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);

279

280

281

SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';

282

SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';

283

SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';

284

SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';

285

SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';

286

287

CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);

288

289

SET enable_seqscan=OFF;

290

291

SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york';

292

SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york';

293

SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york';

294

SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york';

295

SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york';

296

297

RESET enable_seqscan;

298

299

SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city');

300

301

SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text );

302

SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text );

303

SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text );

304

305

SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery');

306

SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery');

307

SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery');

308

309

310

SELECT keyword FROM test_tsquery WHERE keyword @> 'new';

311

SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';

312

SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';

313

SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';

314

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;

315

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;

316

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;

317

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;

318

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;

319

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;

320

321

CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);

322

SET enable_seqscan=OFF;

323

324

SELECT keyword FROM test_tsquery WHERE keyword @> 'new';

325

SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow';

326

SELECT keyword FROM test_tsquery WHERE keyword <@ 'new';

327

SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow';

328

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;

329

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;

330

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;

331

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query;

332

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query;

333

SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query;

334

335

RESET enable_seqscan;

336

337

--test GUC

338

SET default_text_search_config=simple;

339

340

SELECT to_tsvector('SKIES My booKs');

341

SELECT plainto_tsquery('SKIES My booKs');

342

SELECT to_tsquery('SKIES & My | booKs');

343

344

SET default_text_search_config=english;

345

346

SELECT to_tsvector('SKIES My booKs');

347

SELECT plainto_tsquery('SKIES My booKs');

348

SELECT to_tsquery('SKIES & My | booKs');

349

350

--trigger

351

CREATE TRIGGER tsvectorupdate

352

BEFORE UPDATE OR INSERT ON test_tsvector

353

FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);

354

355

SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

356

INSERT INTO test_tsvector (t) VALUES ('345 qwerty');

357

SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

358

UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';

359

SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

360

361

INSERT INTO test_tsvector (t) VALUES ('345 qwerty');

362

363

SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');

Older »