2

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.

1

Proposed Answer

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
    sql := '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;

Will gladly accept any other input. My understanding to make this bullet proof is I should be using the USING command to prevent SQL injection.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.