Problem: Function takes a spatial clause as a parameter of text (more about this later) and appends to the primary sql string.
clause = '((44<=NeLon AND 45>=SeLon AND 37<=NeLat AND 38>=SwLat) OR (SwLon<=45 AND NeLon>=44 AND SwLat<=38 AND NeLat>=37))'
Note the following function:
CREATE OR REPLACE FUNCTION _fconcat(area integer DEFAULT 100, pdate date DEFAULT '2018-01-01', acc real DEFAULT 50.0, clause text DEFAULT NULL) RETURN SETOF temporary.myschema AS $BODY$ DECLARE sqmtokm CONSTANT integer DEFAULT 1000000; sql text; BEGIN SELECT CONCAT ('SELECT * from temporary.myschema WHERE ', ST_Area(ST_GeogFromWkb(geom))\', 'sqmtokm <= ', area, ' AND productdate >= ', pdate, ' AND accuracy <= ', acc, ' AND ', clause, ';') AS sql; RETURN QUERY EXECUTE sql; END;$BODY$ LANGUAGE plpgsql STABLE SET search_path = public;
am specifically concerned with the following.
a) the sql variable is being executed to generate the SETOF table rows results
b) I'm using the CONCAT function to amalgamate the string components to build the sql string but not sure if is the best approach.
c) my clause parameter is of type "text" rather than character varying is this ok
d) I am receiving an error stating that
"sql" is NULL at" RETURN QUERY EXECUTE sql
my primary issue is being able to concatenate an SQL SELECT command and execute it returning a set of table rows.