OpenAPI: defaults of some types are lost

[laughedelic]

Environment

• PostgreSQL version: 10.4
• PostgREST version: 0.5.0.0 (fa1e92f)
• Operating system: macOS 10.13.6

Description of issue

Here's an example table:

create table defaults (
  serial_ serial,
  text_      text      default 'foo',
  date_      date      default '2018-07-24',
  timestamp_ timestamp default '2018-07-24T12:25:23',
  integer_   integer   default 0,
  decimal_   decimal   default 1.1
);

And the produced OpenAPI specification (only definitions.defaults.properties):

"serial_": {
  "format": "integer",
  "type": "integer"
},
"text_": {
  "format": "text",
  "type": "string"
},
"date_": {
  "format": "date",
  "type": "string"
},
"timestamp_": {
  "format": "timestamp without time zone",
  "type": "string"
},
"integer_": {
  "default": 0,
  "format": "integer",
  "type": "integer"
},
"decimal_": {
  "default": 1.1,
  "format": "numeric",
  "type": "number"
}

As you can see only default values for the numeric columns were preserved. Te responsible code is here:

postgrest/src/PostgREST/OpenAPI.hs

Line 67 in fa1e92f

& default_ .~ (decode . toS =<< colDefault c)

I'm not sure, but I think that decode fails on strings because they are not quoted (or quoted with single quotes?).

[steve-chavez]

We use this query(you could run it by replacing $1 with your schema) to get the defaults, it's based on information_schema.columns, and you're right in that these don't always come properly formatted:

select 
  column_name, 
  column_default 
from information_schema.columns 
where table_name = 'defaults';

 column_name |                   column_default
-------------+----------------------------------------------------
 serial_     | nextval('test.defaults_serial__seq'::regclass)
 text_       | 'foo'::text
 date_       | '2018-07-24'::date
 timestamp_  | '2018-07-24 12:25:23'::timestamp without time zone
 integer_    | 0
 decimal_    | 1.1

decode returns Nothing on failure, so we would have to clean column_default first to make it work for all types.

If you'd like to do a PR, an option would be to parse this in decodeColumns(similarly to how we do it in decodeProcs).

Another option would be to do it at the query level, you could use pg string functions, like regexp_split_to_array(to ignore chars after ::) or regexp_replace(to remove single quotes).

[laughedelic]

Thanks for the explanation and the references. It doesn't affect my current work directly, so I'm not sure when/if I will have time to look into this and submit a PR.