Re: Remove Modifiers on Table

Carlos Mennens wrote:
> On Tue, May 17, 2011 at 2:21 PM, Raymond O'Donnell <rod@iol.ie> wrote:
>> That's because of what I just mentioned above. :-) It's not a type: it's
>> just a shortcut. What you need to do instead is something like this:
>>
>>  -- Create the sequence.
>>  create sequence users_id_seq;
>>
>>  -- Tell the column to pull default values from the sequence.
>>  alter table users alter column id set default nextval('users_id_seq');
>>
>>  -- Establish a dependency between the column and the sequence.
>>  alter sequence users_id_seq owned by users.id;
>
> Yup - that explains that the shortcut doesn't work for existing tables
> but only during CREATE TABLE. Otherwise I will need to manually CREATE
> SEQUENCE...blah blah blah.

Yeah.  We went through this one too many times and finally came up with
this function to handle it all for us.  It's crude but it works for us.

create or replace function make_serial(text, text) returns void as
  $$
    declare
      tbl text;
      col text;
      seq text;
      seq_l text;
    begin
      seq := quote_ident($1||'_'||$2||'_seq');
      seq_l := quote_literal($1||'_'||$2||'_seq');
      tbl := quote_ident($1);
      col := quote_ident($2);

      raise notice 'seq = %, tbl = %, col = %', seq, tbl, col;

      execute 'create sequence '||seq;
      execute 'alter table '||tbl||' alter column '||col||' set default nextval('||seq_l||')';
      execute 'alter sequence '||seq||' owned by '||tbl||'.'||col;
      execute 'select setval('||seq_l||', (select max('||col||') from '||tbl||'))';
    end;
  $$
  language plpgsql;

Then you call it thusly:
   select make_serial('users', 'id');

HTH

Bosco.