PostgreSQL function string concatenation

Question

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.

score 1 · Accepted Answer · edited Nov 24 '19 at 10:06

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.

PostgreSQL function string concatenation

1 Answers1