1

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.

closed as off-topic by Jochen Schwarze, Andre Silva, whyzar, Mike T, Evil Genius Sep 13 '18 at 11:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Jochen Schwarze, Andre Silva, whyzar, Mike T, Evil Genius
If this question can be reworded to fit the rules in the help center, please edit the question.

0

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.

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