如何使用 PostgreSQL 数据库中的函数和存储过程从动态表中获取所有数据参数传入的表名

发布于12月31日

我正try 使用函数从动态表中提取数据，但得到错误:"SQL Error[42704]:Error:TYPE"schemaName.p_Dynamicablename"不存在".

CREATE OR REPLACE FUNCTION schemaName."GetAllDataFromDynamicTable"(IN P_DynamicTableName text, IN id integer)
    RETURNS SETOF schemaName."P_DynamicTableName" 
AS $$
BEGIN
  return query
    SELECT * FROM schemaName."P_DynamicTableName" WHERE "Id" = id;
END;
$$
LANGUAGE plpgsql;

我正try 使用存储的过程从动态表中提取数据，但收到错误:"SQL Error[42704]:Error:Type"ml.ableName"is""

注意:能够从表中获取数据.例如:SELECT*FROMSCHEMA NAME."TableName"；

CREATE OR REPLACE FUNCTION schemaName."GetAllDataFromDynamicTable"(tableName character varying)
  RETURNS SETOF schemaName."tableName" 
as $$
BEGIN
  return query 
    select * from schemaName."tableName";
END;
$$
LANGUAGE plpgsql;

推荐答案

正如@禤浩焯所说，动态SQL可以是一种解决方案:

CREATE OR REPLACE FUNCTION GetAllDataFromDynamicTable(IN P_DynamicTableName text, IN id integer)
    RETURNS SETOF record
AS $$
BEGIN
  RETURN QUERY
    EXECUTE FORMAT('SELECT * FROM %I WHERE id = %s', P_DynamicTableName, id) ;
END;
$$
LANGUAGE plpgsql;

但是，在显式定义该函数的输出列之前，调用该函数将导致错误.这仅在所有要动态查询的表都具有相同的列定义的情况下才有可能.

解决方法是将输出记录转换为JSON对象:

CREATE OR REPLACE FUNCTION NewGetAllDataFromDynamicTable(IN P_DynamicTableName text, IN id integer)
    RETURNS SETOF jsonb
AS $$
BEGIN
  RETURN QUERY
    EXECUTE FORMAT('SELECT to_jsonb(t.*) FROM %I AS t WHERE t.id = %s', P_DynamicTableName, id) ;
END;
$$
LANGUAGE plpgsql;

然后，您可以通过不同的方式调用该函数:

SELECT t.* FROM NewGetAllDataFromDynamicTable ('test', 1) AS t将返回json对象.

SELECT (jsonb_populate_record(NULL :: "test", t.*)).* FROM NewGetAllDataFromDynamicTable ('test', 1) AS t将返回表test的列

见dbfiddle