My place list has grown to the point where it is useful to filter it, and operate with a subset. I first created a tiny table:
CREATE TABLE place_filter (
level TEXT,
filter TEXT
);
-- this table must be initialized
INSERT INTO place_filter VALUES ('level_3', '%');
Then I wrote my first set-returning function, by trial-and-error.
CREATE OR REPLACE FUNCTION filtered_places() RETURNS SETOF places AS $$
DECLARE
fl place_filter%ROWTYPE;
pl places%ROWTYPE;
BEGIN
SELECT * FROM place_filter INTO fl;
FOR pl IN EXECUTE
'SELECT * FROM places WHERE ' || QUOTE_IDENT(fl.level) ||
' LIKE ' || QUOTE_LITERAL(fl.filter) || ' OR place_id = 1'
LOOP
RETURN NEXT pl;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql STABLE;
One of the nice things with such a function is that you can call it just the same way as you would do with a table or a view:
$handle = pg_query("SELECT place_id, get_place_name(place_id) AS place_name FROM filtered_places() WHERE place_id > 0 ORDER BY place_name");
I haven’t written a PHP interface for the place filter yet; for now I just issue an "UPDATE place_filter SET level = 'level_3', filter = '%Solum';" from the psql prompt. Usually I do long runs of research within one parish, so I don’t have to “change the filter” often.
