Блог

Наши таблицы довольно странные, и создание новых строк во время наших хранимых процедур становится действительно запутанным, поэтому я пытаюсь создать еще одну отдельную функцию для обработки создания строк, чтобы наши хранимые процедуры оставались чистыми.

Для этого я передаю часть предложения where в качестве параметра, и я использую динамический SQL в PostgreSQL.

У меня есть последовательность, которая отслеживает текущую row_in_transaction (отличную от первичного ключа), и таблица строк, которая отслеживает все строки в каждой транзакции продажи. Когда я вставляю в таблицу строк, я должен использовать последовательность для создания нового значения row_in_transaction, но у меня возникают трудности с этим.

В настоящее время я пытаюсь вызвать nextval для последовательности таким образом:

 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;
  

Но я получаю

ОШИБКА: столбец «v_row_in_trans» не существует

который я правильно определил.

Я попытался использовать nextval без кавычек вокруг него, и я думаю, что в тот момент он просто добавлял nextval к запросу, поэтому он, по сути, передавался как константа, и я получал сообщение об ошибке, что комбинация row_in_transaction и trans_id уже существует.

Вот пик того, на что я смотрю с точки зрения таблицы:

 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
  

Я опустил много неважных деталей, но подумайте о каждой продаже как о транзакции, и в каждой продаже есть несколько покупок (строк). Каждой продаже присвоен уникальный trans_id, и каждая строка в продаже должна иметь строку в таблице строк, содержащую trans_id, и номер строки в транзакции (row_in_trans). Я знаю, что это глупо, что он настроен таким образом, но я буквально ничего не могу с этим поделать. Поэтому, когда я хочу динамически создать новую строку в продаже в хранимой процедуре (возможно, сделка типа buy one get one free), я также должен создать новую строку в таблице строк. В некоторых продажах я создаю более тысячи таких дополнительных строк. Я использую последовательность, чтобы я мог автоматически увеличивать row_in_trans.

Не совсем уверен, как еще я мог бы попытаться передать этот nextval динамически, я не очень разбираюсь в SQL или динамическом SQL. Любые рекомендации будут с благодарностью!

Спасибо!

Последовательность — явно неподходящий инструмент для этой работы. Вы не только выполняете ненужный DDL, но и последовательности не являются безопасными для транзакций или гарантированно без пробелов. (Все они также имеют общее пространство имен, поэтому вашим процедурам, вероятно, придется ждать друг друга раздражающими способами).

Не используйте последовательность. Это примерно так же хорошо для работы, как резиновый цыпленок для молотка.

Догадавшись о том, что вы на самом деле пытаетесь сделать, я теперь вполне уверен, что то, что вы хотите, находится прямо row_number() над окном.

например

 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);
  

Не вдаваясь в причины (@CraigRinger отлично справляется с этим) попробуйте:

 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;