Use postgresql_function

[haydenai-rahul-jain]

Hi everyone, I am trying to add this function onto my database:

CREATE OR REPLACE FUNCTION datadog.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

I am using the postgresql_function terraform resource.
This is what I am doing:

resource "postgresql_function" "datadog_explain_statement" {
  name     = "explain_statement"
  schema   = postgresql_schema.datadog.name
  database = var.database_name
  
  security_definer = true
  strict          = true

  arg {
    name = "l_query"
    type = "text"
  }

  arg {
    name = "explain"
    type = "json"
    mode = "OUT"
  }

  body = <<-EOT
AS $$
DECLARE
  curs REFCURSOR;
  plan JSON;
BEGIN
  OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
  FETCH curs INTO plan;
  CLOSE curs;
  RETURN QUERY SELECT plan;
END;
$$
  EOT
}

However, there seems to be something weird that the first one is doing that the other one isn't doing or something like that. When I execute the first SQL command on the server (manually), my downstream works fine. However, when I make the function using the terraform, it doesn't work. I'm trying to see what's wrong with how the terraform module is creating the function vs the manual.

MY GOAL: replicate as if the function were just made manually on the server.

[vgrigoruk]

Hey @haydenai-rahul-jain

I believe you're forgot to specify return type (missing returns arg).
Below is a working terraform resource (coped from my project):

resource "postgresql_function" "datadog_explain_statement" {
  for_each = var.datadog_monitoring ? { this = postgresql_database.this.name, postgres = "postgres" } : {}

  database = each.value
  schema   = postgresql_schema.datadog[each.key].name

  security_definer = true
  strict           = true

  name = "explain_statement"
  arg {
    name = "l_query"
    type = "text"
  }
  arg {
    name = "explain"
    type = "json"
    mode = "OUT"
  }
  returns = "SETOF json"
  body    = <<-EOF
    AS $$
    DECLARE
    curs REFCURSOR;
    plan JSON;

    BEGIN
      OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
      FETCH curs INTO plan;
      CLOSE curs;
      RETURN QUERY SELECT plan;
    END;
    $$
  EOF
}