Как исправить проблему в процедуре по добавлению записей?

Question

[nurzhannogerbek]

Здравствуйте! Помогите пожалуйста разобраться. Есть таблица RELATIONSHIP в базе данных PostgreSQL, где столбцы STATUS и SEND по умолчанию имеют значение false.

| SURVEY_ID                            | EMPLOYEE | STATUS (default: false) | SEND (default: false) | ORGANIZATION_NAME |
|--------------------------------------|----------|-------------------------|-----------------------|-------------------|
| d5f9c639-13e6-42c1-9043-30783981724b | Mark     | false                   | false                 | Apple             |
| d5f9c639-13e6-42c1-9043-30783981724b | Bob      | true                    | false                 | Google            |

У меня есть процедура, которая выглядит следующим образом:

CREATE OR REPLACE PROCEDURE creator(SURVEY_IDENTIFIER uuid, EMPLOYEES VARCHAR[], ORGANIZATION_NAMES VARCHAR[]) AS $FUNCTION$
	BEGIN
		INSERT INTO RELATIONSHIP (SURVEY_ID, EMPLOYEE, ORGANIZATION_NAME) 
		SELECT 
			SURVEY_IDENTIFIER AS SURVEY_ID,
			EMPLOYEE FROM UNNEST(ARRAY[EMPLOYEES], ARRAY[ORGANIZATION_NAMES]) u(EMPLOYEE, ORGANIZATION_NAME)
		ON CONFLICT ON CONSTRAINT RELATIONSHIP_UNIQUE_KEY 
		DO UPDATE SET ORGANIZATION_NAME = u.ORGANIZATION_NAME
		WHERE NOT STATUS;
	END;
$FUNCTION$ LANGUAGE plpgsql;

Суть процедуры следующая:
1) Если значения, которые мы хотим добавить в таблицу уникальные в рамках столбцов SURVEY_ID и EMPLOYEE необходимо их записать в таблицу.
2) Если значения, которые мы хотим добавить в таблицу не уникальные в рамках столбцов SURVEY_ID и EMPLOYEE, то не нужно их добавлять в таблицу. При этом нужно обновить значение столбца ORGANIZATION_NAME в случаи, если значение в столбце STATUS у существующей записи равна false.

CALL creator(
    'd5f9c639-13e6-42c1-9043-30783981724b',
    ARRAY['Mark', 'Bob', 'Kate'],
    ARRAY['Google', 'Google', 'HP']
);

Другими словами, вызвав данную процедуру следующим образом, я предполагал следующий результат:

| SURVEY_ID                            | EMPLOYEE | ORGANIZATION_NAME | STATUS (default: false) | SEND (default: false) | ORGANIZATION_NAME |
|--------------------------------------|----------|-------------------|-------------------------|-----------------------|-------------------|
| d5f9c639-13e6-42c1-9043-30783981724b | Mark     | Google            | false                   | false                 | Google            |
| d5f9c639-13e6-42c1-9043-30783981724b | Bob      | Apple             | true                    | false                 | Apple             |
| d5f9c639-13e6-42c1-9043-30783981724b | Kate     | HP                | false                   | false                 | HP                |

При попытке вызова процедуру вышеупомянутым способом появляется ошибка:

SQL Error [42601]: ERROR: INSERT has more target columns than expressions.
WHERE: PL/pgSQL function creator(uuid,character varying[],character varying[]) line 3 at SQL statement.

Answer 1

[d-stream]

Что покажет

SELECT 
      SURVEY_IDENTIFIER AS SURVEY_ID,
      EMPLOYEE FROM UNNEST(ARRAY[EMPLOYEES], ARRAY[ORGANIZATION_NAMES]) u(EMPLOYEE, ORGANIZATION_NAME)

?

Comments

[nurzhannogerbek]

Здравствуйте! Вообщем забыл название столбца ORGANIZATION_NAME в указанном вами куске кода:

***
SELECT 
   SURVEY_IDENTIFIER SURVEY_ID,
   EMPLOYEE,
   ORGANIZATION_NAME
FROM UNNEST(ARRAY[EMPLOYEES], ARRAY[ORGANIZATION_NAMES]) u(EMPLOYEE, ORGANIZATION_NAME)
***

При этом уже другая ошибка при вызове процедуры:

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "u"
Где: PL/pgSQL function creator(uuid,character varying[],character varying[]) line 3 at SQL statement

[nurzhannogerbek]

Поменял на:

***
DO UPDATE SET TREE_ORGANIZATION_ID = excluded.TREE_ORGANIZATION_ID
***

Теперь ругается на условие where:

SQL Error [42702]: ERROR: column reference "status" is ambiguous

Есть ли у вас идеи на счет моих упущений?

[nurzhannogerbek]

Вообщем разобрался. Нужно было явно указывать:
WHERE NOT RELATIONSHIP.STATUS

[d-stream]

Вообщем разобрался

идеальный вариант ответа в виде вопроса)

[nurzhannogerbek]

d-stream, единственная проблема которая осталось в данный момент возникает при попытке записи схожие данные. Например:

CALL creator(
	'99c89a24-fff2-4cbc-a542-b1e956a352f9',
	ARRAY['Mark', 'Kate', 'Mark'],
	ARRAY['NEXT', 'U12', 'NEXT']
);

Возникает следующая ошибка:

SQL Error [21000]: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time Tip: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Был бы очень благодарен вашему совету/подсказке :)

[d-stream]

NogerbekNurzhan, дословный перевод в гугле смотрел?

[nurzhannogerbek]

d-stream, ответ конечно эпичный xD

Вообщем нужно было указать DISTINCT в SELECT-e.

[d-stream]

NogerbekNurzhan,

ответ конечно эпичный xD

просто подразумевает прочесть и понять)

А потом уже подумать и сделав выводы - то ли заткнуть дистинктом, то ли слегка переработать