[SQL] Full Text search differences from 8.3 to 8.4.6

[Achilleas Mantzios]

Hello, 
i am currently evaluating the migration cost from 8.3 -> 9.x and in order to do 
that,
i think i must have a confident view of the situation of my app with 8.4.6 as 
an intermediate stage.
I noticed a few minor incompatibilities, but also a change in behaviour in full 
text search:

 Table "public.vmanews"
 Column  |   Type   |  Modifiers
-+--+-
 id  | integer  | not null default 
nextval(('public.vmanews_id_seq'::text)::regclass)
 newid   | integer  | not null
 date| date | not null default now()
 vslid   | integer  | not null
 cat | text | not null
 appname | text | not null
 header  | text | not null
 content | text | not null
 idxfti  | tsvector |
Indexes:
"vmanews_pkey" PRIMARY KEY, btree (id)
"vmanews_vslid_newid" UNIQUE, btree (vslid, newid)
"vmanews_idxfti_idx" gist (idxfti)
"vmanews_vslid" btree (vslid)
Foreign-key constraints:
"$1" FOREIGN KEY (vslid) REFERENCES vessels(id)
Triggers:
vmanews_tsvectorupdate BEFORE INSERT OR UPDATE ON vmanews FOR EACH ROW 
EXECUTE PROCEDURE tsvector_update_trigger('idxfti', 'pg_catalog.english', 
'header', 'content')


in 8.3 :
postg...@dynacom=# SELECT idxfti from vmanews where id=28717;


  idxfti
  
---
 '8':25 'ad':12,34 'job':9,30 'pms':62 'vma':61 'attn':3 'fuel':20 'hour':27 
'kind':43 'main':1,16 'note':6 'pump':21 'sinc':28 'check':18,47 'engin':2,17 
'everi':24 'first':53 'pleas':5 'shock':22,50 'absorb':23,51 'report':55 
'result':58 'vessel':14,38 'vma-pm':60 'inspect':57 'request':44 'respect':49 
'deliveri':36 'opportun':54 'overlook':41 'tech.dept':64 'tks/brgds':63 
'000running':26 'master/cheng':4
(1 row)

(here is the important command)

postg...@dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where 
id=28717;
 ?column?
--
 t
(1 row)

while  in 8.4.6 :
SELECT idxfti from vmanews where id=28717;


  idxfti

 
---
 '000running':26 '8':25 'absorb':23,51 'ad':12,34 'attn':3 'check':18,47 
'deliveri':36 'engin':2,17 'everi':24 'first':53 'fuel':20 'hour':27 
'inspect':57 'job':9,30 'kind':43 'main':1,16 'master/cheng':4 'note':6 
'opportun':54 'overlook':41 'pleas':5 'pms':62 'pump':21 'report':55 
'request':44 'respect':49 'result':58 'shock':22,50 'sinc':28 'tech.dept':64 
'tks/brgds':63 'vessel':14,38 'vma':61 'vma-pm':60
(1 row)

and (the important command in 8.4.6)
dynacom=# SELECT idxfti @@ to_tsquery('overlooked') from vmanews where id=28717;
 ?column?
--
 f
(1 row)


So, searching for "overlooked" which the original word in the text, in 8.3 
idxfti @@ to_tsquery('overlooked') returns true
while in 8.4.6 idxfti @@ to_tsquery('overlooked') returns false.

Is there anything i am missing?

-- 
Achilleas Mantzios

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Translate Function PL/pgSQL to SQL92

[serviciotdf]

Perfect!

The query worked fine!

Answer:

###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS void
AS
$delimiter$
INSERT INTO documentos(descripcion) SELECT $1
WHERE NOT EXISTS ( SELECT id FROM documentos WHERE descripcion = $1 );
INSERT INTO personal ( nombre, idtipodocumento, numdoc )
SELECT $2, ( SELECT id FROM documentos WHERE descripcion = $1 ), $3;
$delimiter$
LANGUAGE SQL;
###

Thanks Filip!

Marcelo

El 16/12/10 08:17, Filip Rembiałkowski escribió:


2010/12/16 serviciotdf >


Hello,

I have a Function in PL/pgSQL and I need to translate it to SQL92,
but I'm stuck.

###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS SETOF personal AS
$delimiter$
BEGIN
PERFORM id from documentos WHERE descripcion = $1;
IF NOT FOUND THEN
   INSERT INTO documentos(descripcion) VALUES($1);
END IF;
INSERT INTO personal(nombre,idtipodocumento,numdoc)
VALUES($2, (SELECT id from documentos WHERE descripcion = $1), $3);
END;
$delimiter$
LANGUAGE plpgsql;
###

Tables

CREATE TABLE documentos
 id serial NOT NULL,
 descripcion character varying(60),
 CONSTRAINT pkdocumentos PRIMARY KEY (id)


CREATE TABLE personal
 id serial NOT NULL,
 nombre character varying(60),
 idtipodocumento smallint NOT NULL,
 numdoc integer,
 CONSTRAINT pkpersonal PRIMARY KEY (id),
 CONSTRAINT fkpdoc FOREIGN KEY (idtipodocumento)
 REFERENCES documentos (id) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE NO ACTION,
 CONSTRAINT unqnumdoc UNIQUE (idtipodocumento, numdoc)



If I understand correctly, you mean translating this function into a 
sequence of plain SQL commands:


INSERT INTO documentos(descripcion) SELECT $1
WHERE NOT EXISTS ( SELECT id FROM documentos WHERE descripcion = $1 );

INSERT INTO personal ( nombre, idtipodocumento, numdoc )
SELECT $2, ( SELECT id FROM documentos WHERE descripcion = $1 ), $3;

of course you will need to bind / pass parameters...

HTH

Filip

Re: [SQL] UPDATE in a specific order

[Luiz K. Matsumura]

Thanks  for reply,

Em 16/12/2010 17:58, Jasen Betts escreveu:

I need to make update of table1 with data on table2 in the order of id
of table2

that looks like EAV. is it?



Err, I don´t know so much about EAV, so I think that isn´t. I´m just 
trying to reproduce a calc in a spreeadsheet.

There are some way to do this with a UPDATE statement ?

to do it with an update statement you need no more than one rows in the
from for each row in the target.

easiest non update statement approach is probably to use a plpgsql
function with a loop.

basically you need to find another way to do it.



Ok! I make a function in plpgsql to do it.

Thank´s again !

*
*