title: PostgreSQL - Writing dynamic sql in stored procedure that returns a result set
body: <p>How can I write a stored procedure that contains a dynamically built SQL statement that returns a result set?  Here is my sample code:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION reporting.report_get_countries_new (
    starts_with varchar,
    ends_with varchar
  )
  RETURNS TABLE (
    country_id integer,
    country_name varchar
  ) AS
  $body$
  DECLARE
    starts_with ALIAS FOR $1;
    ends_with ALIAS FOR $2;
    sql VARCHAR;
  BEGIN
  
      sql = 'SELECT * FROM lookups.countries WHERE lookups.countries.country_name &gt;= ' || starts_with ;
  
      IF ends_with IS NOT NULL THEN
          sql = sql || ' AND lookups.countries.country_name &lt;= ' || ends_with ;
      END IF;
  
      RETURN QUERY EXECUTE sql;
  
  END;
  $body$
  LANGUAGE 'plpgsql'
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 100 ROWS 1000;
  </code></pre>
  
  <p>This code returns an error:</p>
  
  <pre><code>ERROR:  syntax error at or near "RETURN"
  LINE 1: RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE o...
          ^
  QUERY:  RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE omnipay_lookups.countries.country_name &gt;= r
  CONTEXT:  PL/pgSQL function "report_get_countries_new" line 14 at EXECUTE statement
  </code></pre>
  
  <p>I have tried other ways instead of this:</p>
  
  <pre><code>RETURN QUERY EXECUTE sql;
  </code></pre>
  
  <p>Way 1:</p>
  
  <pre><code>RETURN EXECUTE sql;
  </code></pre>
  
  <p>Way 2: </p>
  
  <pre><code>sql = 'RETURN QUERY SELECT * FROM....
  /*later*/
  EXECUTE sql;
  </code></pre>
  
  <p>In all cases without success.</p>
  
  <p>Ultimately I want to write a stored procedure that contains a dynamic sql statement and that returns the result set from the dynamic sql statement.</p>