Возврат строк SETOF из функции PostgreSQL

Question

У меня есть ситуация, когда я хочу вернуть соединение между двумя представлениями. и это много столбцов. Это было довольно легко на сервере SQL. Но в PostgreSQL, когда я делаю соединение. Я получаю сообщение об ошибке "требуется список определений столбцов".

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

CREATE OR REPLACE FUNCTION functionA(username character varying DEFAULT ''::character varying, databaseobject character varying DEFAULT ''::character varying)
  RETURNS SETOF ???? AS
$BODY$
Declare 
SqlString varchar(4000) = '';
BEGIN
IF(UserName = '*') THEN
   Begin
   SqlString  := 'select * from view1 left join ' + databaseobject  + ' as view2 on view1.id = view2.id';
   End;
ELSE
    Begin
    SqlString := 'select * from view3 left join ' + databaseobject  + ' as view2 on view3.id = view2.id';
    End;
END IF; 
execute (SqlString  );
END;
$BODY$

Answer 1

Функция дезинфекции

Руководство имеет все основы для PL/pgSQL. По сути, то, что у вас есть, может быть упрощено / продезинфицировано до:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS ???? AS
$func$
BEGIN

RETURN QUERY EXECUTE
format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
       , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);

END
$func$  LANGUAGE plpgsql;

• Вам не нужны дополнительные экземпляры BEGIN .. END в теле функции, за исключением запуска отдельного блока кода с собственной областью действия, что редко требуется.

• Стандартный оператор конкатенации SQL ||, + это "креативное" дополнение вашего бывшего продавца.

• Не используйте идентификаторы CaMeL-регистра, если вы не заключили их в двойные кавычки. Лучше всего не использовать их вообще:

  • Имена столбцов PostgreSQL чувствительны к регистру?

• varchar(4000) также приспособлен к определенному ограничению SQL Server. Этот тип данных не имеет никакого выигрыша в производительности в Postgres. Используйте его, только если вам действительно нужно ограничение в 4000 символов. Я бы просто использовал text - за исключением того, что нам здесь вообще не нужны переменные, после того как я упростил функцию.

• Если вы не использовали format() Тем не менее, обратитесь к руководству здесь.

Тип возврата

Теперь для вашего реального вопроса: тип возврата для динамического запроса немного сложен, поскольку SQL требует, чтобы функция возвращала четко определенный тип. Если в вашей базе данных уже есть таблица или представление или составной тип, который соответствует списку определений столбцов, который вы хотите вернуть, вы можете просто использовать это:

CREATE FUNCTION foo()
  RETURNS SETOF my_view AS
...

Если вы набираете тип по ходу, вы можете вернуть анонимные записи:

CREATE FUNCTION foo()
  RETURNS SETOF record AS
...

или предоставить список определений столбцов с (самый простой) RETURNS TABLE:

CREATE FUNCTION foo()
  RETURNS TABLE (col1 int, col2 text, ...) AS
...

Недостаток анонимных записей: вы должны предоставлять список определений столбцов при каждом вызове, поэтому я вряд ли когда-либо воспользуюсь этим.

Я бы не использовал SELECT * начать с. Используйте окончательный список столбцов, чтобы возвратить и объявить свой тип возврата соответственно:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date) AS
$func$
BEGIN

RETURN QUERY EXECUTE
format ('SELECT v1.col1, v1.col2, v2.col3
         FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
       , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);

END
$func$;

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

Существуют более продвинутые варианты, но вам может понадобиться сначала изучить основы.

• Рефакторинг функции PL / pgSQL для возврата результатов различных запросов SELECT