How to use DECLARE statement in plpgsql function correctly. ERROR: query has no destination for resu-CodePudding

I have created a function that should return a set of attributes from different tables based on some parameters.

In order to obtain the result, I have created a TYPE and applied multiple INNER JOIN statements

When I call the function I receive the following

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

Here is my code:

SET search_path to 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')

I guess I have made a mistake in the DECLARE statement, but I am not sure how to solve this. Can anyone help?

(I have used multiple tables this is why I am not adding the CREATE TABLE statements here to simplify)

Thank you(:

CodePudding user response：

You can create a simple sql function instead of plpgsql function here. Just check the list of fields provided by the query and which must conform the sponsor_data type in the rigth fields order.

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

Or you can create a PL/pgSQL function to do the same :

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