Postgresql - ОШИБКА: синтаксическая ошибка в состоянии RETURN SQL или рядом с ним: 42601

Я использую Postgresql 9.6 и пытаюсь создать следующую функцию:

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS json
    LANGUAGE 'sql'
    
    
AS $BODY$

CREATE TEMP TABLE t1 AS 
          SELECT * FROM unnest(vals1Arg) AS u(c);

CREATE TEMP TABLE t2 AS 
          SELECT * FROM unnest(vals2Arg) AS u(c);

RETURN QUERY 
    SELECT * FROM "INVENTORY"
        JOIN t1 ON "COLUMN_1" = t1.c
        JOIN t2 ON "COLUMN_2" = t2.c;

$BODY$;

Мое фактическое требование упоминается здесь в отдельном посте

Я получаю следующую синтаксическую ошибку:

ERROR:  syntax error at or near "RETURN"
LINE 12: RETURN QUERY 
         ^
SQL state: 42601

Как мне это решить?

Заранее спасибо!


postgresql sql postgresql-9.6
person code-geek    schedule 17.11.2020    source источник
comment
Не имеет отношения к вашей проблеме, но: вам действительно следует избегать этих ужасных идентификаторов в кавычках. Они доставляют гораздо больше хлопот, чем того стоят. wiki.postgresql.org/wiki/   -  person a_horse_with_no_name    schedule 17.11.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вы не можете использовать return query в функции language sql.

Поскольку вы сделали свои параметры чувствительными к регистру, вы также должны указывать их при их использовании.

Но поскольку вы возвращаете все строки из таблицы "INVENTORY", вам также необходимо объявить свою функцию как returns setof INVENTORY`.

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  CREATE TEMP TABLE t1 AS 
          SELECT * FROM unnest("vals1Arg") AS u(c);

  CREATE TEMP TABLE t2 AS 
          SELECT * FROM unnest("vals2Arg") AS u(c);

  SELECT i.*
  FROM "INVENTORY" i
    JOIN t1 ON i."COLUMN_1" = t1.c
    JOIN t2 ON i."COLUMN_2" = t2.c;
$BODY$;

Но временные таблицы не нужны (и замедлят выполнение запроса). Вы можете использовать unnest непосредственно в запросе:

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  SELECT i.*
  FROM "INVENTORY" i
    JOIN unnest("vals1Arg") AS t1(c) ON i."COLUMN_1" = t1.c
    JOIN unnest("vals2Arg") AS t2(c) ON i."COLUMN_2" = t2.c;
$BODY$;

Но вы действительно хотите присоединиться к параметрам? Может быть, вы имели в виду состояние IN?

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  SELECT i.*
  FROM "INVENTORY" i
  WHERE i."COLUMN_1" = any("vals1Arg")
    AND i."COLUMN_1" = any("vals2Arg");
$BODY$;
person a_horse_with_no_name    schedule 17.11.2020
comment
Размер моих входных аргументов будет огромным с длиной более 3000, и я считаю, что предложение IN замедлит производительность запроса. Разве это не так? По этой причине я попытался создать временную таблицу со значениями аргументов и вместо этого использовать JOIN. - person code-geek; 17.11.2020
comment
@code-geek Я ожидаю, что создание временной таблицы (и вставка строк) будет медленнее, чем присоединение к не вложенному результату. Но вам нужно проверить это с вашими данными и таблицами. - person a_horse_with_no_name; 17.11.2020
comment
Я получаю «org.postgresql.util.PSQLException: ОШИБКА: невозможно передать функции более 100 аргументов». Я вызываю функцию postgres из моего весеннего загрузочного приложения. - person code-geek; 17.11.2020
comment
Ваша функция имеет только два параметра. Звучит так, как будто ваш Java-код передает не два массива, а список значений, но это совсем другой вопрос. - person a_horse_with_no_name; 17.11.2020
comment
Ой! Мой код Java передает объект списка с более чем 3000 значений. Должен ли я передавать массив вместо объекта List? - person code-geek; 17.11.2020