How to query column names dynamically using Postgres/NpgSQL

sql postgresql data-access-layer npgsql
Mr Shoubs picture Mr Shoubs · Apr 7, 2011 · Viewed 18.2k times · Source

I have a filter object to query a table with many columns, and rather than write a condition covering all columns (allowing for optional filtering) like this:

WHERE ((:value0 IS NULL) OR (column_name0 = :value0)) AND ((:value1 IS NULL) OR (column_name1 = :value1)) AND... etc

for every column. Instead, I'd ideally I'd like to be able to pass in the field name as a parameter:

WHERE :column_name0 = :value0 AND column_name1 = :value1 AND... etc

which isn't possible as the columns are required at parse time (similar to this answer given here).

How do you overcome this? - I don't really want to have to maintain the SQL when new columns are added or removed (as you would have to in my first example) and I think it would be dangerous for me to construct the column names into the command string directly as this might allow for sql injection.

Note that this code is behind a web service.

Answer

Eelke picture Eelke · Apr 7, 2011

Just make sure end users cannot provide the column names directly and you should be safe when constructing the query manually. If you need to find out what column names are valid on runtime you can use the following query:

SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='yourtablename'

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More