Обсуждение: Best way to create a sequence generator at run time?

Hi all,

I am trying to write plpgsql that must create a sequence generators during runtime.  The min/max values are provided as
functionparameters. 

The following isn't working for me and I'm not too surprised, but not sure of the best way to proceed.  I am guessing I
needto use PERFORM, EXECUTE or cursors or something? 

==
create or replace function my_number_assigner(
    p_floor int,
    p_ceiling int
) returns void as
$$
    create sequence num_generator minvalue p_floor maxvalue p_ceiling start with p_floor;
end
$$
language plpgsql;
==

 ERROR:  syntax error at or near "$1"
LINE 1: create sequence num_generator minvalue  $1  maxvalue  $2  s...
                                                 ^
QUERY:  create sequence num_generator minvalue  $1  maxvalue  $2  start with  $1
CONTEXT:  SQL statement in PL/PgSQL function "my_number_assigner" near line 35

I presume that the create sequence expression wants to see literals instead of variables, right?  I knew I was going to
runinto this situation sooner or later.  What should I be doing here? 

- Leon

Leon Starr <leon_starr@modelint.com> writes:
> I presume that the create sequence expression wants to see literals instead of variables, right?  I knew I was going
torun into this situation sooner or later.  What should I be doing here? 

You need to construct the CREATE SEQUENCE command as a string then
EXECUTE it.  CREATE SEQUENCE, like most other utility commands, doesn't
handle parameters well.

            regards, tom lane

Thanks, Tom!  Makes perfect sense.

I would like to do something now, just a bit more advanced, but I can't help thinking that there must be a standard
solutionand thought maybe you could point me in the right direction. 

I want to number a relvar (table) as a sub sequence of another relvar.  So if I have Department and Document with
Documentsnumbered within each Department, I am wondering what is the best approach.  It's easy enough to just slap a
sequencetype on Department.Number.  But what about Document?  I would need a new sequence object for each relation
(row)in Department, since each Department handles its own document sequence.  It's almost like I should have an
attributeof Department of type 'sequence generator'.  Is that doable? 

Otherwise, I am thinking of just using a naming scheme where each Department's sequence generator would be named
somethinglike this: <dept_<dept_number>_docnumbering_seq 

The question then is simply:  Am I heading down the right road?  Or is there a simpler solution that is commonly
appliedin this case or some cool trick I am missing? 

Thanks.  - Leon


On Sep 21, 2010, at 2:56 PM, Tom Lane wrote:

> Leon Starr <leon_starr@modelint.com> writes:
>> I presume that the create sequence expression wants to see literals instead of variables, right?  I knew I was going
torun into this situation sooner or later.  What should I be doing here? 
>
> You need to construct the CREATE SEQUENCE command as a string then
> EXECUTE it.  CREATE SEQUENCE, like most other utility commands, doesn't
> handle parameters well.
>
>             regards, tom lane
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

I'm converging on a good solution, but have just one (hopefully) problem left.
I need to get the nextval for a sequence whose name has been stored, and is thus
not available prior to runtime.  I am trying to use EXECUTE, but can't seem to get
it right.  Suggestions?

Here's the function extract in question:

create or replace function ...
) returns bigint as
$$
declare
    self subsystem%rowtype; -- has an attribute where the sequence name is stored
begin
    select * from subsystem into strict self where (name = p_subsystem and domain = p_domain);

    -- self.cnum_generator is a text value holding the name of the previously created sequence
    -- I've tested to ensure that it is holding the correct value, so no worries there, it is a valid sequence

    -- Now here is the trouble - none of these statements seem to work or parse correctly:

    return execute 'nextval(' || self.cnum_generator || ')';  -- NOPE

    new_num := nextval(self.cnum_generator);  -- I didn't expect this one to work, but might as well try, NOPE

    return query execute 'nextval( $1 )' using self.cnum_generator;  -- no good either

    return query execute 'nextval(' || self.cnum_generator || ')';  -- NOPE

    -- accck!  Phhht!  Help!

Well I guess I'll be answering all my own questions today ;)

And the correct answer turns out to be....

    execute 'select nextval(' || quote_literal(self.cnum_generator) || ')' into my_cnum;
    return my_cnum;

Was hoping to avoid the temporary variable (my_cnum), but hey, it works!

And I'm off to the 9.0 release party in SF, see everyone there even though I won't know who anyone is!

- Leon

> I'm converging on a good solution, but have just one (hopefully) problem left.
> I need to get the nextval for a sequence whose name has been stored, and is thus
> not available prior to runtime.  I am trying to use EXECUTE, but can't seem to get
> it right.  Suggestions?
>
> Here's the function extract in question:
>
> create or replace function ...
> ) returns bigint as
> $$
> declare
>     self subsystem%rowtype; -- has an attribute where the sequence name is stored
> begin
>     select * from subsystem into strict self where (name = p_subsystem and domain = p_domain);
>
>     -- self.cnum_generator is a text value holding the name of the previously created sequence
>     -- I've tested to ensure that it is holding the correct value, so no worries there, it is a valid sequence
>
>     -- Now here is the trouble - none of these statements seem to work or parse correctly:
>
>     return execute 'nextval(' || self.cnum_generator || ')';  -- NOPE
>
>     new_num := nextval(self.cnum_generator);  -- I didn't expect this one to work, but might as well try, NOPE
>
>     return query execute 'nextval( $1 )' using self.cnum_generator;  -- no good either
>
>     return query execute 'nextval(' || self.cnum_generator || ')';  -- NOPE
>
>     -- accck!  Phhht!  Help!