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.

We used a 'semidynamic' solution once. The idea was that we could collect a complete set of columns that were interesting for the users, built a view from these and used the application code for selecting subsets. (There was an additional feature which made saving the thusly compiled 'reports' possible, so the good ones could have been reused.) — András Váczi
– András Váczi, Commented Nov 8, 2013 at 22:24

Erwin Brandstetter · Accepted Answer · 2022-02-10 17:46:17Z

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;

This is highly useful. Most likely I will go for the simple SELECT that you've suggested last. — A-K
– A-K, Commented Nov 9, 2013 at 22:29

Stack Exchange Network

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

1 Answer 1

Linked

Hot Network Questions

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

1 Answer 1

Linked

Related

Hot Network Questions