Are there other ways to select a dynamic list of columns?

Question

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that.

1. Using refcursors

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying, 
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE 
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE 
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

This function is very easy to invoke from my Ado.Net client. All I need to do is pass the parameters. However, if I want to test this function from pgAdmin, the result set is open on screen only if I keep my transaction open. This is inconvenient. Of course, it is easy to expose the data as an HTML table or an Excel spreadsheet, but this is kind of a minor inconvenience.

2. Using setof records

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

Unfortunately, this complicates my client code. I cannot issue a simple SELECT like this:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

I must explicitly provide the structure of my result set:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') 
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

This is doable, but inconvenient.

Are there other ways to return dynamic column lists?

Edit to protect against SQL injection, I typically split the comma-separated list and join it against a system view. Anything that is not an actual column name is not returned. I did not mention that originally, just to keep the question short.

Erwin Brandstetter · Accepted Answer · 2022-02-10T17:46:17.553

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   'SELECT ' || colList || '
    FROM   prices
    WHERE  ...
    ORDER  BY ...';
END
$func$;

Call:

CREATE TEMP TABLE t (col1 int, col2 date);
SELECT * FROM select_prices(NULL::t, 'col1, col2');

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2');

If you need multiple tables in the same sessions, employ a sequence to get unique names. See:

Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.

SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;

Are there other ways to select a dynamic list of columns?

1 Answers1

Linked