0

For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves.

 create or replace function 
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0        
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on

When I build this, if I relookup the function definition in datagrip the top part loses my white space I put in there and it looks like this.

create or replace function apqp.apqp_list_gentex_tool_report_getlist(_omni_search text DEFAULT ''::text, _cust_id integer DEFAULT 0) returns TABLE(reason text, program text, mold_number text, rev text, stone_part_number text, part_description text, cust_request_num text, status text, kickoff date, mold_delivered date, first_shot date, ppap_submission_date date, interim_approval_expiration_date date, tool_shop text, manager text, apqp_status text, apqp_list_id integer, comments text)
language sql
as $$

Is there a setting in postgres or someway to save to the database when I CREATE a function that it will save my white space at the top. I'd like to be able to repull the definition while maintaining my white space.

  • 1
    The header part is not save in a formatted way, only the body. – a_horse_with_no_name Aug 7 at 18:28
2

The function body is the only part that is actually saved as string, conserving all white space, code comments etc. Stored in pg_proc.prosrc. It will be parsed etc. at execution time.

The rest of the command is executed immediately and translates to an entry in system catalog pg_proc. What clients like DataGrip later present is the reverse engineered representation derived from system catalog information. Original insignificant whitespace is completely lost in the process. The format is what the client chooses to make it. (DataGrip does not seem to do a great job in the example.)

To preserve original format, you'll have to save your create statement somewhere. Like in the COMMENT on the function - I suggest another layer of dollar quoting, or you'll have to escape single quotes:

COMMENT ON FUNCTION apqp.apqp_list_gentex_tool_report_getlist(text, integer) IS
$com$
create or replace function 
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0        
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on
$com$;

Or a code repository or a user table in the DB or whatever. DataGrip has built-in ways to save and handle files:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.