What does SELECT followed by a SQL command as a string mean

What does something like

SELECT 'DROP INDEX' || 'some other string'

mean?

I know usually you have the column name after the select, like SELECT first_name, but what does it mean when it is in single quotes? I believe the || means to concatenate the strings. Sorry, I’m new to this and couldn’t seem to find anything about this.

EDIT: Sorry I realized my question was not very clear. I meant to ask what does it mean if the SELECT was followed by a SQL command in single quotes? Would it be any different? Would it also just create a column with DROP INDEX as the header? Something like

SELECT 'DROP INDEX' || 'some other string'
FROM ...
WHERE...

Answer

The idea here is to generate SQL statements from database metadata so that you can later execute them as a script. That is a two-step process: first you run an SQL statement that generates SQL statements, then you execute those SQL statements (unless you are using psql, where gexec can do that in a single step).

But if you use the string concatenation operator || to construct the statements, you run the risk of SQL injection.

For example, to delete all tables in a schema, you could generate a script with

SELECT 'DROP TABLE ' || table_schema || '.' || table_name
FROM information_schema.tables
WHERE table_schema = 'my_schema';

Now if there happens to be a table called My-Table, that script would fail, since that is not a standard conforming SQL identifier and would have to be quoted.

And that is also not safe:

SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '"'
FROM information_schema.tables
WHERE table_schema = 'my_schema';

because there could be a table called silly"name.

By crafting tables with names like existing_table"; DROP TABLE "important, you could abuse such a statement to create havoc in the database.

Avoid string concatenation and use format:

SELECT format('DROP TABLE %I.%I', table_schema, table_name)
FROM information_schema.tables
WHERE table_schema = 'my_schema';