Search code examples
postgresqlfunctionplpgsqldynamic-sql

Function to return dynamic set of columns for given table

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.

Solution

  • 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
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('TABLE %s', pg_typeof(_tbl_type));
END
$func$;

    Call (note the syntax!):

    SELECT * FROM public.get_table(NULL::public.users);

    Returns the complete table (with all user columns).

    Wait! How?

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

    TABLE foo is just short for SELECT * FROM foo:

    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:

    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
  LANGUAGE sql 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$;

    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:

    Asides

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

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