Hi all,

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

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 need to 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 run into this situation sooner or later. What should I be doing here?

- Leon

Search Discussions

  • Tom Lane at Sep 21, 2010 at 9:57 pm

    Leon Starr writes:
    I presume that the create sequence expression wants to see literals instead of variables, right? I knew I was going to run 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
  • Leon Starr at Sep 22, 2010 at 12:06 am
    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 solution and 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 Documents numbered within each Department, I am wondering what is the best approach. It's easy enough to just slap a sequence type 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 attribute of 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 something like 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 applied in 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 to run 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
  • Leon Starr at Sep 22, 2010 at 8:20 pm
    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!
  • Leon Starr at Sep 22, 2010 at 9:05 pm
    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!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 21, '10 at 9:44p
activeSep 22, '10 at 9:05p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Leon Starr: 4 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase