Function to return dynamic set of columns for given table

Question

I have a fields table to store column information for other tables:

CREATE TABLE public.fields (
   schema_name varchar(100), 
   table_name  varchar(100), 
   column_text varchar(100), 
   column_name varchar(100), 
   column_type varchar(100) default 'varchar(100)', 
   column_visible boolean
);

And I'd like to create a function to fetch data for a specific table. Just tried sth like this:

create or replace function public.get_table(schema_name text,
                                            table_name text,
                                            active boolean default true)
  returns setof record as $$

declare 
    entity_name text default schema_name || '.' || table_name;
    r record;
begin
    for r in EXECUTE 'select * from ' || entity_name loop
        return next r;
    end loop;
    return;
end
$$
language plpgsql;

With this function I have to specify columns when I call it!

select * from public.get_table('public', 'users') as dept(id int, uname text);

I want to pass schema_name and table_name as parameters to function and get record list, according to column_visible field in public.fields table.

Answer 1

Solution for the simple case

As explained in the referenced answers below, you can use registered (row) types, and thus implicitly declare the return type of a polymorphic function:

CREATE OR REPLACE FUNCTION public.get_table(_tbl_type anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('TABLE %s', pg_typeof(_tbl_type));
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM public.get_table(NULL::public.users);  -- note the syntax!

Returns the complete table (with all user columns).

Wait! How?

Detailed explanation in this related answer, chapter "Various complete table types":

• Refactor a PL/pgSQL function to return the output of various SELECT queries

TABLE foo is just short for SELECT * FROM foo:

• Is there a shortcut for SELECT * FROM?

2 steps for completely dynamic return type

But what you are trying to do is strictly impossible in a single SQL command.

I want to pass schema_name and table_name as parameters to function and get record list, according to column_visible field in public.fields table.

There is no direct way to return an arbitrary selection of columns (return type not known at call time) from a function - or any SQL command. SQL demands to know number, names and types of resulting columns at call time. More in the 2nd chapter of this related answer:

• How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?

There are various workarounds. You could wrap the result in one of the standard document types (json, jsonb, hstore, xml).

Or you generate the query with one function call and execute the result with the next:

CREATE OR REPLACE FUNCTION public.generate_get_table(_schema_name text, _table_name text)
  RETURNS text AS
$func$
   SELECT format('SELECT %s FROM %I.%I'
               , string_agg(quote_ident(column_name), ', ')
               , schema_name
               , table_name)
   FROM   fields
   WHERE  column_visible
   AND    schema_name = _schema_name 
   AND    table_name  = _table_name
   GROUP  BY schema_name, table_name
   ORDER  BY schema_name, table_name;
$func$  LANGUAGE sql;

Call:

SELECT public.generate_get_table('public', 'users');

This create a query of the form:

SELECT usr_id, usr FROM public.users;

Execute it in the 2nd step. (You might want to add column numbers and order columns.)
Or append \gexec in psql to execute the return value immediately. See:

How to force evaluation of subquery before joining / pushing down to foreign server

Be sure to defend against SQL injection:

• INSERT with dynamic table name in trigger function
• Define table and column names as arguments in a plpgsql function?

Asides

varchar(100) does not make much sense for identifiers, which are limited to 63 characters in standard Postgres:

• Maximum characters in labels (table names, columns etc)

If you understand how the object identifier type regclass works, you might replace schema and table name with a singe regclass column.

Answer 2

I think you just need another query to get the list of columns you want.

Maybe something like (this is untested):

create or replace function public.get_table(_schema_name text, _table_name text, active boolean default true) returns setof record as $$
declare
    entity_name text default schema_name || '.' || table_name;
    r record;
    columns varchar;
begin
    -- Get the list of columns
    SELECT string_agg(column_name, ', ')
         INTO columns
         FROM public.fields
         WHERE fields.schema_name = _schema_name
            AND fields.table_name = _table_name
            AND fields.column_visible = TRUE;

    -- Return rows from the specified table
    RETURN QUERY EXECUTE 'select ' || columns || ' from ' || entity_name;

    RETURN;
end
$$
language plpgsql;

Keep in mind that column/table references may need to be surrounded by double quotes if they have certain characters in them.