How to fetch all data from dynamic table using Function & Stored Procedure in the PostgreSQL databas-CodePudding

I am tryign to fetch data from dynamic table using Function but getting error: "SQL Error [42704]: ERROR: type "schemaName.p_dynamictablename" does not exist".

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;

I am tryign to fetch data from dynamic table using Stored Procudure but getting error: "SQL Error [42704]: ERROR: type "ml.tableName" does not exist"

Note: able to fetch data from table. ex: select * from schemaName."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;

CodePudding user response：

As stated by @Adrian, dynamic sql could be a solution :

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;

But calling this function will lead to an error until you can explicitly define the output columns of the function. This is possible only in the case where all the tables to be queried dynamically have the same columns definition.

There is a workaround which consists in converting the output record into a json object :

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;

Then you can call the function in different ways :

SELECT t.* FROM NewGetAllDataFromDynamicTable ('test', 1) AS t will return a json object

SELECT (jsonb_populate_record(NULL :: "test", t.*)).* FROM NewGetAllDataFromDynamicTable ('test', 1) AS t will return the columns of the table test

see dbfiddle