Блог

Я создал функцию, которая должна возвращать набор атрибутов из разных таблиц на основе некоторых параметров.

Чтобы получить результат, я создал ТИП и применил несколько операторов INNER JOIN

Когда я вызываю функцию, я получаю следующее

 ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
 

Вот мой код:

УСТАНОВИТЕ search_path на olympic;

 CREATE OR REPLACE FUNCTION fn_get_info_by_sponsor
       (register_date tb_register.register_ts%type, sponsor_name tb_sponsor.name%type)
RETURNS SETOF sponsor_data AS $
DECLARE 
 data_sponsor sponsor_data;
 email  olympic.email_type;
 sponsor_name tb_finance.sponsor_name%type;
 athlete_name tb_athlete.name%type;
 discilpine_name tb_discipline.name%type;
 round_number  tb_register.round_number%type;
 mark tb_register.register_measure%type;
 register_position tb_register.register_position%type;
 register_date  tb_register.register_ts%type;
 BEGIN 
      SELECT 
           tb_finance.sponsor_name,
           tb_sponsor.email email_type,
           tb_athlete.name AS athlete_name,
           tb_discipline.name AS discipline_name,
           tb_register.round_number,
           tb_register.register_measure,
           tb_register.register_position,
           tb_register.register_ts
      INTO  data_sponsor
      FROM  olympic.tb_sponsor
           
      INNER JOIN olympic.tb_finance

           ON (tb_finance.sponsor_name = tb_sponsor.name)

      INNER JOIN olympic.tb_athlete

           ON tb_athlete.athlete_id = tb_finance.athlete_id

      INNER JOIN olympic.tb_register

           ON tb_register.athlete_id = tb_athlete.athlete_id

      INNER JOIN olympic.tb_discipline

           ON tb_discipline.discipline_id = tb_register.discipline_id

      ORDER BY  tb_register.register_ts;
      
      RETURN NEXT data_sponsor;
 END;
 $LANGUAGE plpgsql;
 

 SELECT * FROM fn_get_info_by_sponsor('2021-06-02 00:00:00','Reebok')
       
 

Я предполагаю, что я допустил ошибку в операторе DECLARE, но я не уверен, как это решить. Кто-нибудь может помочь?

(Я использовал несколько таблиц, поэтому я не добавляю здесь инструкции CREATE TABLE для упрощения)

Спасибо (:

Здесь вы можете создать простую функцию sql вместо функции plpgsql. Просто проверьте список полей, предоставленных запросом, и которые должны соответствовать sponsor_data типу в правильном порядке полей.

 CREATE OR REPLACE FUNCTION fn_get_info_by_sponsor
       (register_date tb_register.register_ts%type, sponsor_name tb_sponsor.name%type)
RETURNS sponsor_data LANGUAGE sql AS $
      SELECT row(
                 tb_sponsor.email,
                 tb_finance.sponsor_name,
                 tb_athlete.name AS athlete_name,
                 tb_discipline.name AS discipline_name,
                 tb_register.round_number,
                 tb_register.register_measure, -- = mark of type CHARACTER(12) ?
                 tb_register.register_position,
                 tb_register.register_ts -- = register_date of type date ?
                ) :: sponsor_data 
      FROM  olympic.tb_sponsor
      INNER JOIN olympic.tb_finance
           ON (tb_finance.sponsor_name = tb_sponsor.name)
      INNER JOIN olympic.tb_athlete
           ON tb_athlete.athlete_id = tb_finance.athlete_id
      INNER JOIN olympic.tb_register
           ON tb_register.athlete_id = tb_athlete.athlete_id
      INNER JOIN olympic.tb_discipline
           ON tb_discipline.discipline_id = tb_register.discipline_id
      ORDER BY  tb_register.register_ts ;
$ 

 SELECT * FROM fn_get_info_by_sponsor('2021-06-02 00:00:00','Reebok')
 

Или вы можете создать функцию PL / pgSQL для выполнения того же :

 CREATE OR REPLACE FUNCTION fn_get_info_by_sponsor
       (register_date tb_register.register_ts%type, sponsor_name tb_sponsor.name%type)
RETURNS sponsor_data LANGUAGE plpgsql AS $
DECLARE
      sp_data sponsor_data ;
BEGIN
      SELECT row(
                 tb_sponsor.email,
                 tb_finance.sponsor_name,
                 tb_athlete.name AS athlete_name,
                 tb_discipline.name AS discipline_name,
                 tb_register.round_number,
                 tb_register.register_measure, -- = mark of type CHARACTER(12) ?
                 tb_register.register_position,
                 tb_register.register_ts -- = register_date of type date ?
                ) :: sponsor_data
       INTO sp_data
       FROM olympic.tb_sponsor
      INNER JOIN olympic.tb_finance
           ON (tb_finance.sponsor_name = tb_sponsor.name)
      INNER JOIN olympic.tb_athlete
           ON tb_athlete.athlete_id = tb_finance.athlete_id
      INNER JOIN olympic.tb_register
           ON tb_register.athlete_id = tb_athlete.athlete_id
      INNER JOIN olympic.tb_discipline
           ON tb_discipline.discipline_id = tb_register.discipline_id
      ORDER BY  tb_register.register_ts ;

      RETURN sp_data ;
END ;  
$ 

 SELECT * FROM fn_get_info_by_sponsor('2021-06-02 00:00:00','Reebok')