title: What are my options for using dynamic sort field/order in postgresql?
body: <p>I have found that i can use dynamic sql to plug in parameters for the sort field and the sort direction. I have also found that to plug those parameters into the dynamic sql one can use "USING param1, param2".</p>
  
  <p>Here is my plpgsql function i am trying to get working. Unfortunately postgresql is throwing an error at the location where the sort direction param is supposed to be interpolated.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION manager_performance_get_urls_by_crawl_id(
      p_account_id character varying(64),
      p_crawl_id character varying(64),
      p_field character varying(20),
      p_direction character varying(20),
      p_page integer,
      p_total integer
  )
  RETURNS TABLE(id character varying(64), source_url text, http_status_code integer, ref_cnt integer, utype rec_url_type, total_size numeric, total_time numeric, secured integer, mime_type character varying(100)) AS $BODY$
  DECLARE
      SQL text := '
          SELECT u.id, u.source_url, u.http_status_code, u.ref_cnt, u.utype, u.total_size, u.total_time, u.secured, u.mime_type FROM url AS u
          JOIN crawl AS c ON(u.crawl_id = c.id)
          JOIN site AS s ON(c.site_id = s.id)
          JOIN person AS p ON(s.person_id = p.id)
          WHERE p.account_id = $1 AND u.crawl_id = $2
          GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $6);
      ';
  BEGIN
      RETURN QUERY EXECUTE SQL USING p_account_id, p_crawl_id, p_field, p_direction, p_page, p_total;
  
  END;
  $BODY$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>The error that is returned:</p>
  
  <pre><code>&lt;b&gt;Warning&lt;/b&gt;:  pg_query_params(): Query failed: ERROR:  syntax error at or near &amp;quot;$4&amp;quot;
  LINE 7:         GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $...
                                            ^
  QUERY:  
          SELECT u.id, u.source_url, u.http_status_code, u.ref_cnt, u.utype, u.total_size, u.total_time, u.secured, u.mime_type FROM url AS u
          JOIN crawl AS c ON(u.crawl_id = c.id)
          JOIN site AS s ON(c.site_id = s.id)
          JOIN person AS p ON(s.person_id = p.id)
          WHERE p.account_id = $1 AND u.crawl_id = $2
          GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $6);
  
  CONTEXT:  PL/pgSQL function manager_performance_get_urls_by_crawl_id(character varying,character varying,character varying,character varying,integer,integer) line 12 at RETURN QUERY
  </code></pre>