Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDdZU-0002fu-DE for pgsql-admin@arkaria.postgresql.org; Thu, 16 Jun 2016 20:07:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDdZS-0006pX-Nd for pgsql-admin@arkaria.postgresql.org; Thu, 16 Jun 2016 20:07:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDSWC-0003qI-3s for pgsql-admin@postgresql.org; Thu, 16 Jun 2016 08:19:16 +0000 Received: from dub004-omc1s5.hotmail.com ([157.55.0.204]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDSW8-0003aU-Nn for pgsql-admin@postgresql.org; Thu, 16 Jun 2016 08:19:14 +0000 Received: from DUB128-W67 ([157.55.0.239]) by DUB004-OMC1S5.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.23008); Thu, 16 Jun 2016 01:19:10 -0700 X-TMN: [GbghPc87N87UuGdfDdoJUSDHR8V18nSl] X-Originating-Email: [l.brodziak@outlook.com] Message-ID: Content-Type: multipart/alternative; boundary="_07de20d8-8efe-415a-b901-06dd53c87152_" From: =?iso-8859-2?B?o3VrYXN6IEJyb2R6aWFr?= To: PostgreSQL Subject: Adding same column to several tables. Date: Thu, 16 Jun 2016 10:19:10 +0200 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 16 Jun 2016 08:19:10.0323 (UTC) FILETIME=[C2451C30:01D1C7A7] X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_07de20d8-8efe-415a-b901-06dd53c87152_ Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hello=2C I have a function to add column to all tables from the list:do$$declare r= elnam pg_class.relname%TYPE=3B kur cursor for select c.relname from pg_cl= ass as c inner join pg_attribute as a on a.attrelid =3D c.oid where a.a= ttname =3D 'some_id' and c.relkind =3D 'r'=3Bbegin open kur=3B LOOP Fe= tch kur into relnam=3B exit when not found=3B EXECUTE 'ALTER TABLE '|= | relnam|| ' add column another_id integer default -1'=3B END LOOP=3B clo= se kur=3BEND=3B$$ I have 22 table names returned by the query used by cursor. Whe I run the f= unction it executes for 1.5 minutes. Is there a way of doing it faster? = = --_07de20d8-8efe-415a-b901-06dd53c87152_ Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable
Hello=2C

I ha= ve a function to add column to all tables from the list:
do<= /div>
$$
declare =3B
 =3B relnam pg_class.r= elname%TYPE=3B
 =3B kur cursor for select c.relname
 =3B from pg_class as c
 =3B  =3B inner join pg_attr= ibute as a on a.attrelid =3D c.oid
 =3B where a.attname =3D '= some_id' and c.relkind =3D 'r'=3B
begin
 =3B open k= ur=3B
 =3B LOOP
 =3B  =3B Fetch kur into re= lnam=3B
 =3B  =3B exit when not found=3B
 = =3B  =3B EXECUTE 'ALTER TABLE '|| relnam|| ' add column another_id inte= ger default -1'=3B
 =3B END LOOP=3B
 =3B close = kur=3B
END=3B
$$

I have = 22 table names returned by the query used by cursor. Whe I run the function= it executes for 1.5 minutes. Is there a way of doing it faster?
=
= --_07de20d8-8efe-415a-b901-06dd53c87152_--