postgresql - Using sequences in dynamic SQL -

postgresql - Using sequences in dynamic SQL -

our tables kinda weird , creating new rows during our store procs gets messy i'm trying create separate function handle creating of rows our store procs remain clean. this, i'm having part of clause passed parameter , i'm using dynamic sql in postgresql.

i have sequence keeps track of current row_in_transaction (different primary key), , row table keeps track of rows in each sale transaction. when insert row table, have utilize sequence generate new row_in_transaction value, i'm having difficulties doing so.

currently, i'm trying phone call nextval on sequence way:

select 'insert row (trans_id, row_in_trans) select ' || p_trans_id || ',' || 'nextval(' || v_row_in_trans || ') sales trans_id =' || p_trans_id || ' , ' || p_where_clause v_sql; execute v_sql;

but i'm getting error: column "v_row_in_trans" not exist, i've defined properly.

i tried using nextval without quotes around it, , think appending nextval @ moment query, beingness passed constant , getting error row_in_transaction , trans_id combo exists.

here's peak @ i'm looking @ 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' current_timestamp) :: character varying, '.', ''); --added timestamp create distinct, in case execute('create sequence '|| v_row_in_trans || ' start '|| max_row_trans_id + 1); --max_row_trans_id select max(id) row table trans_id

i've left lot of nonimportant details out, think of each sale beingness transaction, , having multiple purchases (rows) within each sale. each sale has unique trans_id assigned it, , each row in sale has have row in row table containing trans_id, , row number in transaction (row_in_trans). know stupid set way, literally can't it. when want create new row in sale dynamically in store proc (maybe purchase 1 1 free type deal), have create new row in row table. in sales i'm creating on 1 one thousand of these rows. i'm using sequence can auto increment row_in_trans.

not sure how else seek pass nextval dynamically, i'm not experienced sql or dynamic sql. guidance appreciated!

thanks!

a sequence spectacularly wrong tool job. not executing unnecessary ddl, sequences aren't transaction-safe or guaranteed gapless. (they share namespace, procedures have wait each other in annoying ways).

don't utilize sequence. it's job rubber chicken hammer.

having guessed way you're trying do, i'm confident want row_number() on window.

e.g.

execute format(' insert row (trans_id, row_in_trans) select p_trans_id, row_number() on (order p_trans_id) sales trans_id = p_trans_id , %s', p_where_clause);

postgresql plpgsql dynamic-sql