Dynamic column names in SQL query

[MoritzStefaner]

Hi,

the following query works fine:

SELECT value FROM ccvi WHERE year=${year} AND quarter=${quarter}

However

SELECT ${property} FROM ccvi WHERE year=${year} AND quarter=${quarter}

does not seem to work. Is this a known limitation or a bug?

Thanks for looking into this.

[Fil]

This is a limitation of DuckDB’s prepared statements, which only apply to parameters and not to the shape of the query.

You can however go around it by using the sql tagged template as a function, in a js fenced code block:

```js
const results = await sql([`SELECT ${property} FROM ccvi WHERE year=2010 AND quarter="h2";`]);
```

however this now means that you have to escape the parameters, which is not convenient (and any error might be a security issue if you're using user input).

A mix of both can work:

const results = await sql([`SELECT ${JSON.stringify(property)} FROM ccvi WHERE year=`, ' AND quarter=', ';'], 2010, 'h2');

This generates the following prepared statement:

SELECT "temperature" from ccvi WHERE year=? AND quarter=? with parameters (2010, 'h2').

(You only have to worry about the safety of the 'property' variable, which is included as a string. You don't want user Bobby passing a property named "temperature FROM ccvi LIMIT 1; DROP ALL TABLES -- " or something.)

[MoritzStefaner]

Great, that answers it for me— thank you!

[mbostock]

Another solution is to use a CASE statement. This allows you to reference the column symbolically, even the chosen column is determined by an expression which is controlled by a parameter. You’ll have to enumerate all possible columns explicitly, and all the possible columns must be the same type, but otherwise it should work.

SELECT
  source_id,
  CASE WHEN ${column} = 'ra' THEN ra
    WHEN ${column} = 'dec' THEN dec
    ELSE NULL
    END AS value
FROM gaia
ORDER BY phot_g_mean_mag
LIMIT 10

[MoritzStefaner]

Ah, good to know, thank you!

[fundef1]

a less hardcoded solution use:
SELECT COLUMNS() FROM ... : Select all columns matching the given regular expression from the table.

~~~js
await sql`CREATE OR REPLACE TABLE ccvi AS SELECT generate_series AS id, random() AS A, random() AS B,random() AS C FROM generate_series(1, 25, 1)`;
const property = view(Inputs.select(['A','B','C'], { label: "Property"}));
~~~

~~~sql
SELECT COLUMNS(${property}) FROM ccvi
~~~