CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
Gus
Gus

Reputation: 251

Using sequences in dynamic SQL

Our tables are kinda weird and creating new rows during our stored procedures gets really messy so I'm trying to create another separate function to handle the creating of rows so our stored procedures stay clean.

To do this, I'm having part of the where clause passed as a parameter and I'm using dynamic SQL in PostgreSQL.

I have a sequence that keeps track of the current row_in_transaction (different from a primary key), and a row table that keeps track of all the rows in each sale transaction. When I insert into the row table, I have to use the sequence to generate new row_in_transaction value, but I'm having difficulties doing so.

Currently, I'm trying to call nextval on the sequence this way:

SELECT 'INSERT INTO row (trans_id, row_in_trans)
             SELECT '  ||  p_trans_id || ',' ||  'nextval(' || v_row_in_trans || ')
              FROM sales 
             WHERE trans_id =' || p_trans_id || ' AND ' || p_where_clause into v_sql;

EXECUTE v_sql;

But I'm getting

ERROR: column "v_row_in_trans" does not exist

which I've defined properly.

I tried using nextval without quotes around it, and I think it was just appending the nextval at that moment to the query, so it was essentially being passed as a constant and I was getting an error that the row_in_transaction and trans_id combo already exists.

Here's a peak at what I'm looking at table-wise:

CREATE TABLE row
(
    id serial NOT NULL,
    trans_id integer,
    row_in_trans integer,
    CONSTRAINT row_pkey PRIMARY KEY (id),
    CONSTRAINT row_trans UNIQUE (trans_id , row_in_trans)
)

CREATE TABLE sales
(
    trans_id integer,
    row_id integer,
    row_in_trans

    CONSTRAINT sale_row_fk 
        FOREIGN KEY (row_id)
        REFERENCES row (id) MATCH SIMPLE
            ON UPDATE NO ACTION ON DELETE NO ACTION
}


v_row_in_trans := 'row_in_trans'  || REPLACE(EXTRACT('epoch' FROM CURRENT_TIMESTAMP) :: character varying, '.', ''); --added the timestamp to make it distinct, just in case
EXECUTE('CREATE SEQUENCE '|| v_row_in_trans   || ' START '||   max_row_trans_id + 1); --max_row_trans_id is just a select max(id) from the row table for that trans_id

I've left a lot of nonimportant details out, but think of each sale as being a transaction, and having multiple purchases (rows) within each sale. Each sale has a unique trans_id assigned to it, and each row in a sale has a to have a row in the row table containing the trans_id, and the row number in the transaction (row_in_trans). I know its stupid that its set up that way, but I literally can't do anything about it. So when I want to create a new row in sale dynamically in a stored procedure (maybe a buy one get one free type deal), I have to also create a new row in the row table. In some sales I'm creating over one thousand of these extra rows. I'm using a sequence so that I can auto increment row_in_trans.

Not really sure how else I could try to pass this nextval dynamically, I'm not very experienced with SQL or Dynamic SQL. Any guidance would be greatly appreciated!

Thanks!

Upvotes: 0

Views: 1271

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324691

A sequence is spectacularly the wrong tool for this job. Not only are you executing unnecessary DDL, but sequences aren't transaction-safe or guaranteed gapless. (They also all share a namespace, so your procedures are likely to have to wait for each other in annoying ways).

Don't use a sequence. It's about as good for the job as a rubber chicken is as a hammer.

Having guessed my way to what you're actually trying to do, I'm now fairly confident that what you want is just row_number() over a window.

e.g.

EXECUTE format('
  INSERT INTO row (trans_id, row_in_trans)
  SELECT 
    p_trans_id, 
    row_number() OVER (ORDER BY p_trans_id)
  FROM sales 
  WHERE trans_id = p_trans_id AND %s', p_where_clause);

Upvotes: 1

Fast Engy
Fast Engy

Reputation: 2011

Without getting into the why (@CraigRinger is doing a superb job at that) try:

SELECT 'INSERT INTO row (trans_id, row_in_trans)
             SELECT '  ||  p_trans_id || ',' ||  nextval('v_row_in_trans')
              FROM sales 
             WHERE trans_id =' || p_trans_id || ' AND ' || p_where_clause into v_sql;

EXECUTE v_sql;

Upvotes: 0

Related Questions