所以這是功能
`
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;
$$;
`
現在該函式回傳查詢，必須手動執行才能產生結果。我想對其進行編輯，這樣您就不必這樣做了，但是在呼叫該函式時它會立即執行。
我閱讀了有關回傳查詢執行命令的資訊，但我沒有設法使其作業。
我不希望有完整的解決方案，但我們將不勝感激一些提示或幫助。
uj5u.com熱心網友回復：
您需要更改函式以回傳表，而不是字串。
可以使用以下方法輕松完成回傳查詢結果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';
$$;
請注意，您需要調整returns table (...)零件中的資料型別。
然后，您可以像這樣使用該函式：
select *
from creategridfromchart('blabla');
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/527311.html
標籤：PostgreSQL功能
上一篇：在腳本中實作多執行緒/并行處理