Unable to import existing functions

[joshsouza]

Hi there,

I'm attempting to leverage the new postgresql_function resource to codify existing functions. However, I seem to be unable to decipher what format the import statement needs to be in to have any success, and it doesn't appear that I can overwrite the existing functions (there's a CREATE OR REPLACE code path, but that is only triggered on updates, not something I can control at the user level).

Terraform Version

Terraform v1.1.9
on linux_amd64

Affected Resource(s)

• postgresql_function

Terraform Configuration Files

resource "postgresql_function" "diag_session_state" {
  name = "diag_session_state"

  returns = <<-RETURNS
    TABLE (session_state text, cnt bigint)
  RETURNS

  schema = "public"

  body = <<-BODY
  AS $$
  BEGIN
    Raise info 'Session state count';
    return query
    select state, count(*)
    from pg_stat_activity
    where datname is not null
    and pid != pg_backend_pid() group by state;
  END;
  $$ LANGUAGE 'plpgsql';
BODY
}

Things I've tried:

terraform import 'postgresql_function.diag_session_state' 'diag_session_state'
terraform import 'postgresql_function.diag_session_state' 'public.diag_session_state'
terraform import 'postgresql_function.diag_session_state' 'public.diag_session_state()'
terraform import 'postgresql_function.diag_session_state' '<OID of function in DB>'

Debug Output

All of the above imports get the same equivalent error:

│ Error: Cannot import non-existent remote object
│
│ While attempting to import an existing object to
│ "postgresql_function.diag_session_state", the provider detected that no
│ object exists with the given id. Only pre-existing objects can be imported; check that the id is correct and that it is
│ associated with the provider's configured region or endpoint, or use "terraform apply" to create a new remote object for
│ this resource.
╵

and attempting to create without import gives:

Error: pq: function "diag_session_state" already exists with same argument types

Which is accurate

Expected Behavior

I should be able to run a terraform import, or alternatively should have a replace flag available at the resource level to trigger the logic on this line: https://github.com/cyrilgdn/terraform-provider-postgresql/blob/924be532d9f90cf5296f46f4dfb1ea331f3b22ca/postgresql/resource_postgresql_function.go#L267 to perform an OR REPLACE during initial creation so that I can write over the existing function.

Actual Behavior

Failure to import, due to the resource id not existing (maybe I'm passing in the wrong format? Docs don't have any mentioned.) Failure to create, due to the function already existing (and refusing to overwrite)

Steps to Reproduce

1. Set up a provider that will operate against an existing database
2. Create a function in that database via tool/commandline
3. Create HCL to create that same function with Terraform
4. terraform apply - Error, refuses to overwrite
5. terraform import <resource name> <function name> - Error, id not found

Important Factoids

The docs don't mention any importing syntax, so maybe it's not supported, but the code clearly has a path that should work. I just can't quite reverse-engineer what format the 'id' field needs to be in (I've tried passing the direct oid from the existing function, but that didn't work either). Perhaps I'm expecting something to work that has never worked, but I figured that if it was just a matter of documentation, this issue may help get that brought to light.