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 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.

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

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 the way 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 something like:

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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general