Thread: stored procs
Is is possible within a stored procedure to read all the tables in a schema into a list? From that list and for each table is it possible to find the foreign keys in that table? From that list of foreign keys, is it possible to find out which field in which table the FK corresponds to? I need to extract this meta-data for a project. Regards, J.V.
On 09/28/11 6:33 PM, J.V. wrote: > Is is possible within a stored procedure to read all the tables in a > schema into a list? > > From that list and for each table is it possible to find the foreign > keys in that table? > > From that list of foreign keys, is it possible to find out which field > in which table the FK corresponds to? > > I need to extract this meta-data for a project. > that metadata should all be available in the information_schema. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 29/09/11 02:33, J.V. wrote: > Is is possible within a stored procedure to read all the tables in a > schema into a list? [snip] > I need to extract this meta-data for a project. Apart from information_schema mentioned elsewhere, start psql with -E and then try \dt and similar - it will show you the queries it uses. -- Richard Huxton Archonet Ltd
Yes, I know that one. What I want to do however is within a stored procedure make a call and store all tables in a given schema in a list. Is this something you can answer? thanks J.V. On 9/29/2011 3:25 AM, Richard Huxton wrote: > On 29/09/11 02:33, J.V. wrote: >> Is is possible within a stored procedure to read all the tables in a >> schema into a list? > [snip] >> I need to extract this meta-data for a project. > > Apart from information_schema mentioned elsewhere, start psql with -E > and then try \dt and similar - it will show you the queries it uses. >
On 09/30/11 12:24 AM, J.V. wrote: > What I want to do however is within a stored procedure make a call and > store all tables in a given schema in a list. so procedure1 calls procedure2 (not sure why this extra level of procedures, but I'm playing along) and procedure2 does SELECT table_name from information_schema.tables where table_schema='someschemaname'; and returns this data as a 'list' (whatever you mean by that) ? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 09/30/11 1:13 AM, J.V. wrote:
> thanks for the help, we have a production schema with 80 tables and a
> few of the tables have consumed the max limit for the id field, so I
> have to write a program (stored functions would be the fastest way to
> do this), that will go and drop the sequence, create & start at 1
> again, and re-sequence every table and then make sure all the foreign
> keys in every table point to where they are supposed to.
you shouldn't have to drop the sequence, just
setval('sequencename',max(idfield)+1); after resequencing the ID's with
a generate_series... ...
or, convert the fields to bigint which are 64 bit and not likely to wrap
around any time in this century.... (sequences already use bigint)
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 09/30/11 1:23 AM, John R Pierce wrote:
> convert the fields to bigint which are 64 bit and not likely to wrap
> around any time in this century
indeed, this is as simple as ...
alter table tblname alter column id type bigint;
of course, you'll need to alter all the FK fields that refer to it too,
and I don't know if that entangles this.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On 09/30/11 1:28 AM, John R Pierce wrote: > On 09/30/11 1:23 AM, John R Pierce wrote: >> convert the fields to bigint which are 64 bit and not likely to wrap >> around any time in this century > > indeed, this is as simple as ... > > alter table tblname alter column id type bigint; > > of course, you'll need to alter all the FK fields that refer to it > too, and I don't know if that entangles this. > > yes, quite easy. test=# create table xxx (id serial primary key, val text); NOTICE: CREATE TABLE will create implicit sequence "xxx_id_seq" for serial column "xxx.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "xxx_pkey" for table "xxx" CREATE TABLE test=# create table yyy (id serial primary key, xxx_id int references xxx(id)); NOTICE: CREATE TABLE will create implicit sequence "yyy_id_seq" for serial column "yyy.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "yyy_pkey" for table "yyy" CREATE TABLE test=# alter table xxx alter column id type bigint; ALTER TABLE test=# alter table yyy alter column xxx_id type bigint; ALTER TABLE voila! -- john r pierce N 37, W 122 santa cruz ca mid-left coast
For tables that already exist and have a foreign key relationship, is there an equivalent alter statement for the statement below? Does this mean that if table xxx.id primary key value changes, the foreign key value will change as well? If this is the case, then would not have to match up all the foreign keys to the new re-sequenced id. Also another question: if the "id serial primary key", do I need to create a sequence at all? Or is that managed by the database? Typically you have to link the id to the database sequence object, but it looks as in this case no "CREATE SEQUENCE" would be needed. thanks J.V. On 9/30/2011 2:45 AM, John R Pierce wrote: > create table yyy (id serial primary key, xxx_id int references xxx(id));
On 30 September 2011 11:20, J.V. <jvsrvcs@gmail.com> wrote: > For tables that already exist and have a foreign key relationship, is there > an equivalent alter statement for the statement below? > > Does this mean that if table xxx.id primary key value changes, the foreign > key value will change as well? They do if you tell them to CASCADE on UPDATE. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
On 09/30/11 2:09 AM, J.V. wrote: > Some tables have millions of rows, well, something like UPDATE tablename SET id=generate_series(1,numberofrows); will update every row to a sequential value. However, I have no idea how you would match the foreign key references in other tables to these new sequence values. anything that updates a field on a million rows will be causing every row to be updated... postgres never updates anything in place, it will be copying the whole row to a new one (this is how it implements MVCC, and its fundamental to the architecture, there's nothing you can do to override this behavior). -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On 09/30/2011 11:41 PM, John R Pierce wrote: > On 09/30/11 2:09 AM, J.V. wrote: >> Some tables have millions of rows, > > well, something like UPDATE tablename SET > id=generate_series(1,numberofrows); will update every row to a > sequential value. However, I have no idea how you would match the > foreign key references in other tables to these new sequence values. There are two ways to do that. You can add a *new* column for the new keys and generate them. Then you add a matching empty column to each referencing table and fill it using a JOIN against the old key and ALTER each referencing table to add the FOREIGN KEY before dropping the old key column. Finally, you drop the old key column in the main table. Alternately, you can ALTER all the foreign key references to be CASCADE, then UPDATE the main table to set new keys. PostgreSQL will cascade the changes to the referencing tables. The second method is simpler and you might think it'd be faster, but it probably won't be. The first method requires one sequential re-write of each table when the UPDATE to fill the new key columns runs, but is otherwise just a series of JOINs on key columns. On the other hand, the second method requires *lots* of *random* writes all over the place on the referencing tables, and is likely to be a lot slower even if you have indexes on your foreign key columns. If you *don't* have indexes on your foreign key columns the second method is going to be spectacularly, amazingly, stunningly slow. -- Craig Ringer