Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

allow objects as first class parameters to plpgsql fns #15

Open
mgoldfield opened this issue Jul 12, 2021 · 0 comments
Open

allow objects as first class parameters to plpgsql fns #15

mgoldfield opened this issue Jul 12, 2021 · 0 comments

Comments

@mgoldfield
Copy link

Allow the ability to write functions which take objects (e.g. tables, schemas, functions) as parameters, with the code generated being sql or plpgsql, not stuck in an obscuring execute format(...) block where all syntax checks are lost.

Many applications of this in assemble permissions. One example:
every permissions insert has to execute a block of code similar to:

  for v_level_name in select level_name
    from assemble.table_permission_levels
    where hierarchy_position <= (
      select spl.hierarchy_position
      from assemble.table_permission_levels spl
      where spl.level_name = NEW.permission_level)
  loop
    if v_level_name != 'none' then
      execute format($q$
        select assemble.tables_grant_%s(t, %s)
        from assemble.tables t
        where t.id = %s
      $q$, 
        v_level_name, quote_literal(NEW.role_id), quote_literal(NEW.table_id)
      );
    end if;
  end loop;

This block needs to be in every permissions insert function for databases, schemas, tables, columns, views, forms, and groups, with the only thing changing being the name of the table. It would be safer and cleaner to have this encapsulated in a function that takes the table (or table name or regclass) as a parameter and can generate all of the blocks of code needed in a safe and syntax-checked way.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant