1

Let's say I create the following function:

create or replace function my_new_function(
    p_something text default null
)
etc....

But if I go to check the function on PostgreSQL I see this instead:

CREATE OR REPLACE FUNCTION my_new_function(p_something text default NULL::text)

Why does it do that?

Chessbrain
  • 1,223
  • 3
  • 17
  • 25

1 Answers1

2

In short: you stumbled across an internal detail, of how postgres handles datatypes in default values. You likely just want to keep it.

As described in Why do I need to cast NULL to column type?, the default type of NULL is unknown in most cases, so the type info is appended when you set a default. (The same can happen to column defaults also)

When postgres displays the function, it likely does not remember how you created it, so it displays null::text. For column defaults, postgres sometimes recognizes that the user would find the typecast irrelevant and removes it, but sometimes it does not (not sure when exactly). So if you state default null that can be seen as default NULL::varchar(30) for instance. For tables, this can be "fixed" with ... drop default, but I think this would make the function parameter required.

tosh007
  • 21
  • 2