pg自定义函数动态生成表名

目录

一、需求

二、踩坑记录

三、解决方案

---

一、需求

想在postgres数据库中动态查询【'table_2023'、'table_2024'...】这种格式表的数据。

例如：

      今天是'2023-12-22'号，查询语句为'select * from table_2023';

      今天是'2024-12-22'号，查询语句为'select * from table_2024';

      以此类推。。。

二、踩坑记录

一开始打算sql拼接出表名，但是很快发现问题

select * 
from (
select concat('table_',extract(year from now())) as this_year
) a
;

执行后并不是我想要的结果，查询出来的只是我拼接出来的内容 😭

三、解决方案

最后放弃了sql拼接，决定直接自定义函数解决。

第一种方法，创建自定义函数查询当年表的数据。

--生成查询'table_'当年格式数据表数据
CREATE OR REPLACE FUNCTION "get_thisyear_table"()
  RETURNS TABLE("col1" int8,"col2" varchar(255),"col3" int8) AS $BODY$
    DECLARE
    subquery  varchar;
    pre_sql   varchar;
    this_year int;
    begin
    pre_sql:='select extract(year from now())';
    EXECUTE pre_sql into this_year;
    subquery := 'select col1,col2,col3 from "table_'||this_year||'"';
    RETURN QUERY execute subquery;
    END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000
  ;
  
--调用函数查看数据  
select * from get_thisyear_table();

 

第二种方法，创建新表存储符合格式的所有表union all数据。

--创建新表union_tables存储多个动态表union all之后的数据
--(ps:只有2个及以上要union all的表时才可以)
CREATE OR REPLACE FUNCTION get_unionall_table()
  RETURNS void AS $BODY$
DECLARE
    sqlSearch TEXT DEFAULT '';
    drop_sql  TEXT DEFAULT '';
    rec_data RECORD;
    cur_data REFCURSOR;
    cur_tablename TEXT;
BEGIN-- Open the cursor
OPEN cur_data FOR SELECT
tablename 
FROM pg_tables 
WHERE tablename LIKE 'table_%' 
  AND LENGTH ( tablename ) = LENGTH ( 'table_xxxx' );
FETCH cur_data INTO rec_data;
while
FOUND
LOOP-- fetch row into the film
cur_tablename := rec_data.tablename;
FETCH NEXT 
FROM
cur_data INTO rec_data;
sqlSearch := sqlSearch || ' select col1,col2, col3 from ' || cur_tablename || ' union all ';

END LOOP;
-- Close the cursor
CLOSE cur_data;
drop_sql :='drop table if exists union_tables';
EXECUTE drop_sql;
sqlSearch := substr( sqlSearch, 1, "length" ( sqlSearch ) - LENGTH ( 'union all ' ) );
sqlSearch := 'create table IF NOT EXISTS  union_tables as (' || sqlSearch|| ')';
EXECUTE sqlSearch;
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000
;

--调用函数
select get_unionall_table();
--验证数据
select * from union_tables;