module development: postgresql_funcs

[stefanvdm-em]

SUMMARY

Create, alter or drop PostgreSQL functions.

ISSUE TYPE

• Module development

COMPONENT NAME

postgresql_funcs

ADDITIONAL INFORMATION

Example usage:

- name: Sum of integers function
  community.postgresql.postgresql_funcs:
      function: sum_of_integers
      language: plpgsql
      return_type: integer
      arguments:
          - "a ingeter"
          - "b integer"
      source: "BEGIN RETURN a+b; END;"

PROPOSED ANSIBLE ARGUMENTS

Considering the most basic and minimal usage of CREATE FUNCTION

CREATE FUNCTION return_some_integer()
    RETURNS integer
    LANGUAGE 'plpgsql'
    AS
    $$
    BEGIN
        RETURN 1;
    END
    $$;

we can see that we will always need:

• a name for our function,
• the return type,
• the language of the function, and
• some source code for the definition of the function.

From my limited personal use of PostgreSQL functions, I have used the following attributes the most:

• behaviour (IMMUTABLE, STABLE, VOLATILE),
• null argument calling (CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT, STRICT), and
• security (SECURITY INVOKER, SECURITY DEFINER).

My suggestion would be to include the above-mentioned arguments initially and then expand to include all remaining options later.

APPROACH

Since PostgreSQL functions are named objects, similar to tables, I think it best to base the development of postgresql_funcs on postgresql_table.

[Andersson007]

@stefanvdm-em hello, thanks for the suggestion! Here are several things we should take a consideration when we think about adding new modules:

• it should cover as much underlying functionality as possible, i.e. should be able to create complex functions (i'm not a specialist though)
• it should be truly idempotent, not like postgresql_idx or postgresql_table that i created when i was a green newbie, they are not idempotent and operate based on name mostly. i would rather be happy to see them removed from the collection one day. They feel like useless peaces considering presence of the postgresql_query module.
• new modules should satisfy Ansible dev conventions

postgresql_table is a bad example imo as, as I wrote, it's not fully idempotent. For example, if i change the column order, the module will ignore it and will say "nothing changed" but it's important, isn't it? On the other hand if we change the order, the table will be fully rebuilt which will lead to the data loss. So there are a lot of questions.

@stefanvdm-em if you have a draft of the module, you can submit it here, of course. If you don't have but want to write it, please take the above into consideration and the question whether postgresql_query can be effectively used instead or not?

[stefanvdm-em]

Hi @Andersson007,

Thank you for the feedback and guidance. I have started to work on a draft for postgresql_funcs. Still, I will shift some gears first to consider if it provides an improved interaction over the existing postgresql_query interactions with functions and, if so, how to expose as much of the underlying functionality as possible.

[Andersson007]

Hi @Andersson007,

Thank you for the feedback and guidance. I have started to work on a draft for postgresql_funcs. Still, I will shift some gears first to consider if it provides an improved interaction over the existing postgresql_query interactions with functions and, if so, how to expose as much of the underlying functionality as possible.

@stefanvdm-em sounds like a plan, thank you!