SQL loop by shell

While PL/pgSQL has LOOP flow for recursive operations, SQL or psql doesn’t have one.
If you want to loop without PL/pgSQL, utilize bash:

for id in $(psql -U<user> <database> -t -c "SELECT id FROM <table>"); do
  # any recursive query
  psql -U<user> <database> -t -c "CALL some_procedure($id)"
done

psql -t in the outer query excludes extra outputs like column header, so variable id gets each id from table.
Inside for loop, you can issue any query with bash variable substitution.

For example, CALL stored procedure requires external loop for multiple operations.

中馬崇尋
Chuma Takahiro

Related articles

sqldef: Benefis and usage

sqldef is a CLI tool to maintain RDBMS schema along with corresponding configs. It is a ridgepole clone written in Go. Unlike standard migrations in popular web frameworks, their configs define the whole schema instead of the differences between schema versions. With traditional migrations, you cannot confirm the implemented schema in the database until the migration is applied.……

SQL loop by shell

While PL/pgSQL has LOOP flow for recursive operations, SQL or psql doesn’t have one. If you want to loop without PL/pgSQL, utilize bash: for id in $(psql -U<user> <database> -t -c "SELECT id FROM <table>"); do # any recursive query psql -U<user> <database> -t -c "CALL some_procedure($id)" done psql -t in the outer query excludes extra outputs like column header, so variable id gets each id from table.……

INSERT duplicate records into PostgreSQL

DBMS has the unique index feature that protects against INSERTing duplicate records, and at a minimum, the primary key must be unique. In cases like testing, where you might want to enter the same data, including the primary key, you need to be clever about how you use INSERT statement.……

Ridgepole: Benefits and usage

The standard database definition tool for Ruby on Rails is ActiveRecord’s Migration. It allows version control by writing database definition changes in Ruby scripts instead of SQL. However, with migration, as the number of DB definition changes increases, innumerable files will be generated and the outlook will be poor. By using ridgepole, DB definitions can be aggregated into one configuration file using the same notation as migration, and it can be operated with idempotent settings.……