Re: [SQL] Multiple DB join
On 8/15/06, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > I previously thought of doing the full text search indexing thing...but i> had a intution that the full text search thing is for fields which have very> large strings...but in my case the strings are not above 200 chars in No, it's for data where you're going to be searching in random piecesof the text. I think you should look at tsearch2, probably.Will the full text search indexing help me achive a good speed in searching keywords??? If you had any way to enforce bounded searches, it'd be a differentmatter: strings with initial matches but an unbound end are fast. (You can do it the other way, too, by some tricks with reversing thestrings.)can someone plz ellaborate a little about ways we can enforce bounded searches?. I'm basically trying a simple search i.e. trying to find name of authors user enters into a interface against the 20 miliions records in my db. Can anyone suggest a good way to perform this kind of search ?. Thanks,Sumeet.
Re: [SQL] Multiple DB join
On Fri, Aug 18, 2006 at 11:07:24AM -0400, Sumeet wrote: > > Will the full text search indexing help me achive a good speed in searching > keywords??? I think this depends on how you use it. > can someone plz ellaborate a little about ways we can enforce bounded > searches?. I'm basically trying a simple search > i.e. trying to find name of authors user enters into a interface against the > 20 miliions records in my db. Can anyone suggest a good way to perform this > kind of search ?. The thing is, if they're keywords, why are you treating them as fragments? Traditionally, keywords are not substrings, but full barewords. A bareword match should be fast, because it's looking for the whole string. So you shouldn't need the "%" characters. Maybe the problem that you have something like the following. If your data is stored like this subject | keyword subject1| keyword1 keyword2 keyword3 and you want every subject that matches on keyword2, then you have to search this with SELECT subject WHERE keyword = '%keyword2%'. The reason you have to do that is that your data is badly normalised. Is that it? A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] trigger needs to check in multiple tables.
Hello.I'm running a simple query inside a function that is associated with a trigger: SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta=NEW.codigo_destino ;I am getting this error when running the code on pgadmin IIIERROR: NEW used in query that is not in a rule QUERY: SELECT tipo FROM producto WHERE codigo_cuenta=NEW.codigo_destinoI don't know what is failing. I'm running postgresql 8.1.4 on windows. Maybe I have to enable something? Here is the complete code of the function, sure there will be things wrong, but the statement failing is just next to BEGINCREATE OR REPLACE FUNCTION movimientosenoficina() RETURNS BOOLEAN AS $$ DECLARE esoficinacorrecta BOOLEAN; esfechacorrecta BOOLEAN; op INTEGER; fondo INTEGER; imp INTEGER; tipocuenta INTEGER; BEGIN SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta=NEW.codigo_destino; IF (tipocuenta=71) THEN IF (op=51) THEN esfechacorrecta:=primerosmes(); esoficinacorrecta:=oficinacorrecta(NEW.codigo,NEW.codigo_oficina); ELSIF (NOT op=11 OR NOT op=12) RAISE EXCEPTION 'Operación no permitida sobre cuentas a plazo'; END IF; ELSIF (tipocuenta=60) --Permiten todo tipo de operaciones ELSIF (tipocuenta=10) IF (op=54) THEN SELECT saldo INTO fondo FROM producto WHERE codigo_cuenta = NEW.destino; SELECT cantidad INT imp FROM movimiento WHERE codigo = NEW.codigo; IF (fondo!=imp) THEN RAISE EXCEPTION 'Se ha de transferir todo el saldo de la cuenta' ELSE oficinacorrecta(NEW.codigo,NEW.codigo_oficina); -- Ponemos la cuenta inactiva si retiramos los fondos UPDATE cuenta_a_plazo SET activo=TRUE WHERE codigo_cuenta = NEW.destino; ENDIF; ENDIF; ELSE RAISE EXCEPTION 'Tipo de cuenta no existente'; END IF; RETURN NEW; END;$$ LANGUAGE plpgsql;/* Este trigger se activa una vez hemos comenzado la transaccion y hemos insertado en movimiento_oficina nos sirve para evitar operaciones no definidas o erroneas sobre los distintos tipos de cuentas*/CREATE TRIGGER validamovimientosenoficina BEFORE INSERT OR UPDATE ON CLIENTEFOR EACH ROW EXECUTE PROCEDURE movimientosenoficina(); -- Jacobo García López de Araujo
Re: [SQL] trigger needs to check in multiple tables.
On Fri, Aug 18, 2006 at 07:17:27PM +0200, Jacobo Garca wrote: > I'm running a simple query inside a function that is associated with a > trigger: > >SELECT tipo INTO tipocuenta FROM producto WHERE codigo_cuenta= > NEW.codigo_destino; > > I am getting this error when running the code on pgadmin III > > ERROR: NEW used in query that is not in a rule > QUERY: SELECT tipo FROM producto WHERE codigo_cuenta=NEW.codigo_destino Trigger functions must return TRIGGER; the function you posted returns BOOLEAN. If you change the return type to TRIGGER then the above error should go away. However, the function also has several syntax errors. >ELSIF (NOT op=11 OR NOT op=12) The above line and a few others are missing THEN. >SELECT cantidad INT imp FROM movimiento WHERE codigo => NEW.codigo; INT should be INTO. >RAISE EXCEPTION 'Se ha de transferir todo el saldo de la > cuenta' This line is missing a trailing semicolon. >oficinacorrecta(NEW.codigo,NEW.codigo_oficina); Use PERFORM to call a function for its effects and ignore its return value. Or did you mean to assign the return value to a variable? >ENDIF; ENDIF should be END IF. If you make the indicated changes then the function should be created successfully. I didn't look closely at the logic, so whether it'll actually work is another matter ;-) -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
