Hiya list,
I was wondering if this is possible or not. If yes how?
I want to use a changing where condition on the query, subject to the
value I pass in to the function. I have tried the below but it fails in
use. This is with PostgreSql 8.3.1.
The query will end up a bit more complicated than below. With a set
where clause it returns the rows/results expected.
CREATE OR REPLACE FUNCTION test(period_type text) RETURNS SETOF test_type AS
$BODY$
DECLARE
o record;
r test_type;
where_text character varying;
BEGIN
IF period_type = 'current' THEN
where_text := 'WHERE field1 IS NULL';
elseif period_type = 'old' THEN
where_text := 'WHERE field1 IS NOT NULL';
elseif ....
(more conditions here with various where clauses)
else
RETURN;
end if;
FOR o IN
SELECT distinct(col_id) as id
FROM table1
JOIN table2
ON col_id = t2_t1_id
where_text <-- this bit
LOOP
FOR r IN
SELECT a.col1,a.col2,b.col3,b.col4
FROM table2 as a
JOIN table3 as b
ON t2_t1_id = t3_t1_id
WHERE t2_t1_id = o.id
LOOP
RETURN NEXT r;
END LOOP;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;