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

I need to create a sequence in a stored procedure.

First I need to select a value from a table and set the sequence start
value to that value.

We have a table that does not have a sequence on it, so I want to select
the max value, increment by one
and then start the sequence there.

We have to do this on three databases, I have figured out how to do this
in Oracle (build a string and the EXECUTE IMMEDIATE <myString>, but have
not figured out how to do this with PostgreSQL.

any ideas?

thanks


J.V.

On Fri, Apr 27, 2012 at 10:37 AM, J.V. <jvsrvcs@gmail.com> wrote:
> I need to create a sequence in a stored procedure.
>
> First I need to select a value from a table and set the sequence start value
> to that value.
>
> We have a table that does not have a sequence on it, so I want to select the
> max value, increment by one
> and then start the sequence there.
>
> We have to do this on three databases, I have figured out how to do this in
> Oracle (build a string and the EXECUTE IMMEDIATE <myString>, but have not
> figured out how to do this with PostgreSQL.

just about any sql is allowed from within a function, including CREATE
SEQUENCE.  functions can even create functions and execute them.

merlin

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.

so I need to construct a string that contains the create sequence
statement and execute that string, at least this is the way it is done
in Oracle.

I do not know how to use a variable in a create sequence statement in
PostgreSQL.

J.V.

On 4/27/2012 9:51 AM, Merlin Moncure wrote:
> On Fri, Apr 27, 2012 at 10:37 AM, J.V.<jvsrvcs@gmail.com>  wrote:
>> I need to create a sequence in a stored procedure.
>>
>> First I need to select a value from a table and set the sequence start value
>> to that value.
>>
>> We have a table that does not have a sequence on it, so I want to select the
>> max value, increment by one
>> and then start the sequence there.
>>
>> We have to do this on three databases, I have figured out how to do this in
>> Oracle (build a string and the EXECUTE IMMEDIATE<myString>, but have not
>> figured out how to do this with PostgreSQL.
> just about any sql is allowed from within a function, including CREATE
> SEQUENCE.  functions can even create functions and execute them.
>
> merlin
>

On Fri, Apr 27, 2012 at 11:35 AM, J.V. <jvsrvcs@gmail.com> 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.
>
> so I need to construct a string that contains the create sequence statement
> and execute that string, at least this is the way it is done in Oracle.
>
> I do not know how to use a variable in a create sequence statement in
> PostgreSQL.

oh I see. try this:


postgres=# do
$$
declare
  s int default 3;
begin
  execute 'create sequence v start ' ||  s;
end;
$$ language plpgsql;
DO

merlin

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

On Apr 27, 2012, at 9:35 AM, 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
needto first do a select from a table to get a max value, store that in a variable and then use that variable in a
createsequence sql statement. 

Another approach would be to create the sequence then set the value - you can do that without needing anything more
thanSQL: 

  create sequence foo
  select setval('foo', (select max(bar) from baz)


> so I need to construct a string that contains the create sequence statement and execute that string, at least this is
theway it is done in Oracle. 
>
> I do not know how to use a variable in a create sequence statement in PostgreSQL.

It depends on the language you're using. For plpgsql it's covered in more detail in the docs, but you could do
somethinglike: 

  create function make_sequence() returns void as $$
    declare
      newvalue integer;
    begin
      select max(bar)+1 from baz into newvalue;
      execute 'create sequence foo start ' || newvalue;
    end;
  $$ language plpgsql;


Cheers,
  Steve

>
> J.V.
>
> On 4/27/2012 9:51 AM, Merlin Moncure wrote:
>> On Fri, Apr 27, 2012 at 10:37 AM, J.V.<jvsrvcs@gmail.com>  wrote:
>>> I need to create a sequence in a stored procedure.
>>>
>>> First I need to select a value from a table and set the sequence start value
>>> to that value.
>>>
>>> We have a table that does not have a sequence on it, so I want to select the
>>> max value, increment by one
>>> and then start the sequence there.
>>>
>>> We have to do this on three databases, I have figured out how to do this in
>>> Oracle (build a string and the EXECUTE IMMEDIATE<myString>, but have not
>>> figured out how to do this with PostgreSQL.
>> just about any sql is allowed from within a function, including CREATE
>> SEQUENCE.  functions can even create functions and execute them.
>>
>> merlin
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general