How to edit this PostgreSQL function so that query is executed right away when it is called?-CodePudding

So this is the function

create function createGridFromChart(p_y_value character varying) returns text
    language plpgsql
as
$$
DECLARE
    v_query text;

    BEGIN

    v_query = 'SELECT result,
       dateG,
       (SELECT name player1 from mts_players WHERE mts_game.id_player1 = mts_players.id),
       (SELECT name player2 from mts_players WHERE mts_game.id_player2 = mts_players.id),
       (SELECT name player3 from mts_players WHERE mts_game.id_player3 = mts_players.id),
       (SELECT name player4 from mts_players WHERE mts_game.id_player4 = mts_players.id),
       trophy, winners
from mts_game
where dateG> CURRENT_DATE-30 and ((SELECT name from mts_players WHERE mts_game.id_player1 = mts_players.id) = '''|| p_y_value || ''' or (SELECT name from mts_players WHERE mts_game.id_player2 = mts_players.id) = '''|| p_y_value ||''' or (SELECT name from mts_players WHERE mts_game.id_player3 = mts_players.id) = '''|| p_y_value ||''' or (SELECT name from mts_players WHERE mts_game.id_player4 = mts_players.id) ='''|| p_y_value ||''' )
order by dateG';
    
    RETURN v_query;

    END;
$$;

Right now the function returns the query and it has to be manually executed in order to produce results. I want to edit it so that you don't have to do that, but it's executed right away when the function is called.

I read about return query execute command but I haven't managed to make it work.

I don't expect complete solution but some tips or help would be appreciated.

CodePudding user response：

You need to change the function to return a table, rather than a string.

Returning the result of a query can easily be done using language sql:

create function createGridFromChart(p_y_value character varying) 
  returns table (result text, dateg date, name1 text, name2 text, name3 text, name4 text, tropy text, winners text) --<<< adjust types here!!
  language sql
as
$$
   SELECT result,
          dateG,
          (SELECT name player1 from mts_players WHERE mts_game.id_player1 = mts_players.id),
          (SELECT name player2 from mts_players WHERE mts_game.id_player2 = mts_players.id),
          (SELECT name player3 from mts_players WHERE mts_game.id_player3 = mts_players.id),
          (SELECT name player4 from mts_players WHERE mts_game.id_player4 = mts_players.id),
          trophy, 
          winners
   from mts_game
   where dateG > CURRENT_DATE-30 
   and (   (SELECT name from mts_players WHERE mts_game.id_player1 = mts_players.id) = p_y_value 
        or (SELECT name from mts_players WHERE mts_game.id_player2 = mts_players.id) = p_y_value 
        or (SELECT name from mts_players WHERE mts_game.id_player3 = mts_players.id) = p_y_value 
        or (SELECT name from mts_players WHERE mts_game.id_player4 = mts_players.id) = p_y_value )
   order by dateG';
$$;

Note that you need to adjust the data types in the returns table (...) part.

You can then use the function like this:

select * 
from creategridfromchart('blabla');