1

I'm trying to run the following command

UPDATE attachments SET colname=TRIM(colname);

on every column name in a SQLite table.

I found the following approach to getting a list of column names in an answer to "How to get a list of column names on sqlite3 / iPhone?", namely

SELECT name FROM PRAGMA_TABLE_INFO('your_table');

which seems like a nice, clean, simple way to do it. But this method is specific to SQLite, per PRAGMA Statements.

In any case, I'd like a solution that runs entirely in SQLite. I'm actually calling SQLite from Lua, using LuaSQL. So I could run the loop in Lua if necessary. But doing it inside SQLite just seems cleaner.

It would be even better if it's a general method that works for any RDBMS. But that may not be possible, and if it is, it would probably be more complicated

1

You can't do it in pure sqlite; column names have to be hardcoded in the query (also why they can't be bound to parameters), even if there was a way to use UPDATE in a loop, which there isn't.

1

Your approach to get a list of the columns is a good start. Then you can dynamically build a SQL statement to perform the tasks you like. However you need a way to loop over the results and execute the query. This is usually done with the help of the application you use to connect to the database, in your case Lua. If you are using the command line interface sqlite3, you can make use of a temporary file for this:

.mode list
.headers off
.once temp.sql
SELECT 'UPDATE attachments SET "' || name || '" = TRIM("' || name || '");' FROM PRAGMA_TABLE_INFO('attachments');
.read temp.sql

In PostgreSQL's psql you have the \gexec command to do such a task directly.

On you other question how to access the schema / catalog in a generic, not database specific way: This is indeed difficult. As you correctly identified, the PRAGMA_TABLE_INFO command is specific to SQLite. Every database has it's own ways to access this kind of data. There is however a feature called information_schema in the SQL Standard, that does aim to provide a non-vender dependent view on the schema. Unfortunately, this is not widely implemented by the vendors, see an overview on Wikipedia. If you happen to use a DBMS that does implement this part of the standard, the query to get the column names would be something along

SELECT column_name FROM information_schema.columns WHERE table_name = 'attachments';

You should also consider the data type of the selected columns before you apply a trim function on them as that would only be supported for character data types.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.