Обсуждение: pgSQL function for: SELECT ... WHERE EXISTS ( SELECT true myFunc() )
pgSQL function for: SELECT ... WHERE EXISTS ( SELECT true myFunc() )
От
tamir@imp.univie.ac.at (Ido Tamir)
Дата:
Hi, I have a table where I need to select probes if at least three of their values (experimental data) are above a certain treshhold Table: probes val1 val2 val3 val4 one 2 -2 3 1 ... This works with the function below. However I would like also to be able to integrate this function somehow into a bigger SQL SELECT statement with muliple exists (simplified): SELECT * FROM probes WHERE EXISTS ( SELECT true FROM data WHERE data.probe = probes.probe AND val1 > 2 ); val1 > 2 should be replaced with some pgSQL function. How do I get the current probe postgresql evaluates into the function and return that it passed? If it is possible, how big is the performace hit for calling these functions? Thank you very much for your answers Ido M. Tamir CREATE OR REPLACE FUNCTION getMin3Above( INTEGER ) RETURNS Boolean AS ' DECLARE minVal ALIAS FOR $1; values RECORD; fit INTEGER; passed BOOLEAN; BEGIN FOR values IN SELECT * FROM data LOOP fit := 0; passed := false; IF abs(values.val1) > minVal THEN fit := fit + 1; END IF; IF abs(values.val2) > minVal THEN fit := fit + 1; END IF; IF abs(values.val3) > minVal THEN fit := fit + 1; END IF; IF abs(values.val4) > minVal THEN fit := fit + 1; END IF; IF fit > 2 THEN passed := true; END IF; RAISE NOTICE ''probe: % passed: %'', values.probe, passed ; END LOOP; RETURN passed; END; ' LANGUAGE 'plpgsql'; CREATE TABLE probes( probe VARCHAR(10) primary key ); CREATE TABLE data( probe VARCHAR(10) references probes( probe), val1 INT, val2 INT, val3 INT, val4 INT ); INSERT INTO probes VALUES( 'one' ); INSERT INTO probes VALUES( 'two' ); INSERT INTO probes VALUES( 'three' ); INSERT INTO probes VALUES( 'four'); INSERT INTO probes VALUES( 'five' ); INSERT INTO data VALUES( 'one', 2, 2, 2, 2 ); INSERT INTO data VALUES( 'two', 3, 3, 3, 1 ); INSERT INTO data VALUES( 'three', 1, 3, 3, 1 ); INSERT INTO data VALUES( 'four', 1, 3, 1, 1 ); INSERT INTO data VALUES( 'five', 3, 3, 3, 3 );