Re: how to create a sequence in a stored proc?

On 27/04/2012 17:35, J.V. wrote:
> Right, I understand that fully, and have used SQL inside a stored proc
> before, but in this case as I mentioned, I need to first do a select
> from a table to get a max value, store that in a variable and then use
> that variable in a create sequence sql statement.

Something like this, off the top of my head and untested:

create or replace function make_sequence_for_table()
returns void
as
$$
declare
  max_value integer;
begin
  select max(my_column) into max_value from my_table;

   create sequence my_sequence
    start (max_value + 1)
    owned by my_table.my_column;

  alter table my_table
    alter column my_column
    set default nextval('my_sequence');

  return;
end;
$$
language plpgsql;


For extra marks, pass the table name in as a parameter, construct the
SQL as a string and execute it using EXECUTE (the pl/pgsql version, not
the command for executing prepared statements).

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie