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.