5

I have several tables with the exact same structure and I need to update a value in all tables.

In order to do so I tried to build the following script:

DO
$do$
DECLARE
  i pg_tables%rowtype;
BEGIN
FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
LOOP
    UPDATE i.tablename SET name = replace(name, '.js', '.ts');
END LOOP;
END
$do$;

I could see that i.tablename has the correct value (I inserted in a tmp table to check), but the update fails.

name: error
length: 223
severity: ERROR
code: 42P01
internalPosition: 8
internalQuery: UPDATE i."tablename" SET name = replace(name, '.js', '.ts')
where: PL/pgSQL function inline_code_block line 7 at SQL statement
file: parse_relation.c
line: 965
routine: parserOpenTable

Just plugin i.tablename on the UPDATE statement doesn't work.

Is there a way to make it work? Or an easier way to update all tables at once?

7
  • 3
    execute format('UPDATE %I SET name = replace(name, ''.js'', ''.ts'')', i.tablename);
    – BrunoLM
    May 24 '17 at 13:37
  • 1
    @dezso: The dupe was not right. UPDATE <> ADD COLUMN. May 25 '17 at 2:39
  • @ErwinBrandstetter it was right, as you see, the OP found a solution based on the dupe (dynamic SQL, where any object name is substituted programmatically).
    – dezso
    May 31 '17 at 14:40
  • @dezso: The OP posted a solution, good - with a sneaky bug, though. Your link was certainly useful. But does that make it a duplicate? I wouldn't think so. Jun 1 '17 at 1:52
  • @ErwinBrandstetter wait, what is the bug on the solution I posted?
    – BrunoLM
    Jun 1 '17 at 11:31
6

You're actually close.. first create some test data..

CREATE TABLE foo_knex_migrations ( name )
  AS VALUES ('test.js'),('test2.js'),('bicycles');
CREATE TABLE bar_knex_migrations AS TABLE foo_knex_migrations;
CREATE TABLE baz_knex_migrations AS TABLE foo_knex_migrations;

Next, we're going to use EXECUTE...FORMAT(), with %I.

DO
$do$
  DECLARE
    i pg_tables%rowtype;
  BEGIN
  FOR i IN SELECT * FROM pg_catalog.pg_tables where schemaname like 'public' and tablename like '%_knex_migrations'
  LOOP
    EXECUTE FORMAT(
      $$
        UPDATE %I
        SET name = replace(name, '.js', '.ts');
      $$,
      i.tablename
    );
  END LOOP;
  END
$do$;


TABLE baz_knex_migrations ;
   name   
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

test=# TABLE foo_knex_migrations ;
   name   
----------
 test.ts
 test2.ts
 bicycles
(3 rows)

As a side note,

  1. In general, you should be using information_schema which is standardized for simple things like this, and where speed doesn't matter.
  2. You should probably be checking to see if the UPDATE needs to run by adding a WHERE clause. Otherwise, you're rewriting the table for nothing.
  3. In SQL, we don't use naming conventions like this. There is no need for that. If knex_migrations has multiple tables, consider CREATE SCHEMA knex_migrations to store them, rather than searing through the catalog based on a naming convention for all tables.
2

You posted a basically working solution as comment.

A weaknesses remains, though: If you don't schema-qualify the table name in the UPDATE and the schema does not happen to be the first in the current search path, the wrong tables might be updated. Consider instead:

DO
$do$
DECLARE
   sch text := 'public';  -- your schema here
   tbl text;
BEGIN
   FOR tbl IN
      SELECT tablename FROM pg_catalog.pg_tables
      WHERE  schemaname = sch
      AND    tablename LIKE '%_knex_migrations'
   LOOP
      EXECUTE format($$UPDATE %I.%I SET name = replace(name, '.js', '.ts')$$, sch, tbl); 
   END LOOP;
END
$do$;

Related:

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.