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

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