碰到这样一个需求, postgresql数据库里每天会生成一张新表,表名的规律是 “前缀_YYYY-MM-DD”,前缀加日期的形式,这样sql查询时,查询一个表里的数据,但是表名是动态变化的。
记录下本次解决过程,着急的可以直接点击看结果。
- 最开始的想法是像SQLserver一样 查询前定义变量,在查,参考1 类似:
DECLARE @myvar INT
SET @myvar = 5
SELECT *
FROM somewhere
WHERE something = @myvar
但是pgsql没有这样的功能,有个with()查询,它可以作为变量,例如:
WITH tableName AS ( VALUES ( concat ( 'table_', CURRENT_DATE ) ) )
SELECT * FROM tableName
但事实上,这样只是把表名存在了这个变量中,查询只是把这个变量从临时表中读取出来,没有实现效果。
- 之后,在网络上搜索,得到下面的思路。
创建一个函数,函数的结果集是查询该动态表名的结果集,查询该结果集获得结果,参考2。
CREATE OR REPLACE FUNCTION "get_table"("tname" varchar)
RETURNS SETOF "pg_catalog"."record" AS $BODY$
DECLARE
subquery varchar;
begin
subquery := 'select * from "'||tname||'"';
execute subquery;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
SELECT * FROM get_table ( concat ( 'Monitor_', CURRENT_DATE ) ) AS monitor ( ID int4, TIME TIMESTAMP, Program TEXT )
但是查询结果为空
继续搜索,参考3,直接返回table,
CREATE OR REPLACE FUNCTION "get_table"("tname" varchar)
RETURNS TABLE("ID" int4, "TIME" timestamp, "Program" text) AS $BODY$
DECLARE
subquery varchar;
begin
subquery := 'select * from "'||tname||'"';
execute subquery;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
但是查询结果依旧是空。
注意到,在函数中使用了'EXECUTE'
参考4,在函数中,使用EXECUTE返回表
修改函数,添加RETURN QUERY
CREATE OR REPLACE FUNCTION "get_table"("tname" varchar)
RETURNS TABLE("ID" int4, "TIME" timestamp, "Program" text) AS $BODY$
DECLARE
subquery varchar;
begin
subquery := 'select * from "'||tname||'"';
RETURN QUERY execute subquery;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000
SELECT * FROM get_table ( concat ( 'Monitor_', CURRENT_DATE ) )
成功获取数据,顺利解决😊