create get functions

function_get_create.sql

create or replace function it.function_get_create(_schema_dot_table text, _single_pkey_column_name text DEFAULT 'id'::text, _single_pkey_column_type text DEFAULT 'INTEGER'::text) returns void
	language plpgsql
as $$
    /*
  ***************************************************************************************************
  Create Date:          2019-03-25
  Author:               Dan Van Den Bosch
  Used By:              SQL consoles.
  Why:                  This is to be ran by SQL developers on tables to create functions that get 1 row
  Usage: SELECT it.function_get_create('myschema.mytable')
  ****************************************************************************************************
  */
        DECLARE
        _returns_table_lister TEXT;
        _select_lister        TEXT;
        _table_name           TEXT;
        BEGIN
                SELECT split_part(_schema_dot_table, '.', 2)
                INTO _table_name;

                SELECT
                       string_agg(       pg_attribute.attname || ' ' || pg_attribute.atttypid::regtype, ', ')  AS returns_table_lister
                     , string_agg(_table_name || '.' || pg_attribute.attname,E'\n' || '              , ')     AS select_lister
                       -- more attributes?
                FROM   pg_catalog.pg_attribute
                WHERE  pg_attribute.attrelid = _schema_dot_table::regclass  -- table name, optionally schema-qualified
                AND    pg_attribute.attnum > 0
                AND    NOT pg_attribute.attisdropped
                INTO
                  _returns_table_lister
                , _select_lister
                ;

   PERFORM it.command
      -- INSERT INTO it.animals ( animal) VALUES --this is for debugging purposes
                      ('CREATE FUNCTION ' || _schema_dot_table || '_get (_' || _single_pkey_column_name || ' ' || _single_pkey_column_type || ') RETURNS TABLE (' || _returns_table_lister || ') LANGUAGE plpgsql AS'    || E'\n' ||
                       '$body$'                                                                                                                                                                                          || E'\n' ||
                       '/*'                                                                                                                                                                                              || E'\n' ||
                       '=================================================='                                                                                                                                              || E'\n' ||
                       'Author:     ' || current_user::TEXT || ''                                                                                                                                                        || E'\n' ||
                       'Created At: ' || current_date::TEXT                                                                                                                                                              || E'\n' ||
                       'Note: This function was generated using it.function_get_create()'                                                                                                                                || E'\n' ||
                       '=================================================='                                                                                                                                              || E'\n' ||
                       '*/'                                                                                                                                                                                              || E'\n' ||
                       'DECLARE'                                                                                                                                                                                         || E'\n' ||
                       '        --===========try catch vars==========='                                                                                                                                                  || E'\n' ||
                       '        _returned_sqlstate     TEXT;'                                                                                                                                                            || E'\n' ||
                       '        _message_text          TEXT;'                                                                                                                                                            || E'\n' ||
                       '        _pg_exception_detail   TEXT;'                                                                                                                                                            || E'\n' ||
                       '        _pg_exception_hint     TEXT;'                                                                                                                                                            || E'\n' ||
                       '        _pg_exception_context  TEXT;'                                                                                                                                                            || E'\n' ||
                       '        --===================================='                                                                                                                                                  || E'\n' ||
                       ''                                                                                                                                                                                                || E'\n' ||
                       'BEGIN'                                                                                                                                                                                           || E'\n' ||
                       '        --============= SET VARIABLE VALUES =============='                                                                                                                                      || E'\n' ||
                       '        RETURN QUERY  '                                                                                                                                                                          || E'\n' ||
                       '         SELECT ' || _select_lister                                                                                                                                                              || E'\n' ||
                       '           FROM ' || _schema_dot_table                                                                                                                                                           || E'\n' ||
                       '          WHERE ' || _table_name || '.' || _single_pkey_column_name || ' = ' || '_' || _single_pkey_column_name || ';'                                                                           || E'\n' ||
                       ' --/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////'                        || E'\n' ||
                       ''                                                                                                                                                                                                || E'\n' ||
                       '        --===========CATCH CODE FOR ERRORS============='                                                                                                                                         || E'\n' ||
                       '     EXCEPTION WHEN others THEN'                                                                                                                                                                 || E'\n' ||
                       '         GET STACKED DIAGNOSTICS'                                                                                                                                                                || E'\n' ||
                       '      _returned_sqlstate    = RETURNED_SQLSTATE'                                                                                                                                                 || E'\n' ||
                       '    , _message_text         = MESSAGE_TEXT'                                                                                                                                                      || E'\n' ||
                       '    , _pg_exception_detail  = PG_EXCEPTION_DETAIL'                                                                                                                                               || E'\n' ||
                       '    , _pg_exception_hint    = PG_EXCEPTION_HINT'                                                                                                                                                 || E'\n' ||
                       '    , _pg_exception_context = PG_EXCEPTION_CONTEXT;'                                                                                                                                             || E'\n' ||
                       ''                                                                                                                                                                                                || E'\n' ||
                       '    _pg_exception_hint = _pg_exception_hint; --TODO add your parameters here.'                                                                                                                   || E'\n' ||
                       ''                                                                                                                                                                                                || E'\n' ||
                       '    PERFORM it.pg_func_exceptions_insert ('                                                                                                                                                      || E'\n' ||
                       '                                              _returned_sqlstate'                                                                                                                                || E'\n' ||
                       '                                            , _message_text'                                                                                                                                     || E'\n' ||
                       '                                            , _pg_exception_detail'                                                                                                                              || E'\n' ||
                       '                                            , _pg_exception_hint'                                                                                                                                || E'\n' ||
                       '                                            , _pg_exception_context'                                                                                                                             || E'\n' ||
                       '                                            , current_query()'                                                                                                                                   || E'\n' ||
                       '                                            , FALSE  --false -> email will be sent to DBA upon function failure'                                                                                 || E'\n' ||
                       '                                           );'                                                                                                                                                   || E'\n' ||
                       '     RAISE EXCEPTION E''Got exception:'                                                                                                                                                          || E'\n' ||
                       '                        state  : %'                                                                                                                                                              || E'\n' ||
                       '                        message: %'                                                                                                                                                              || E'\n' ||
                       '                        detail : %'                                                                                                                                                              || E'\n' ||
                       '                        hint   : %'                                                                                                                                                              || E'\n' ||
                       '                        context: %'', _returned_sqlstate, _message_text, _pg_exception_detail, _pg_exception_hint, _pg_exception_context;'                                                       || E'\n' ||
                       '        --============END CATCH CODE========================='                                                                                                                                   || E'\n' ||
                       'END;'                                                                                                                                                                                            || E'\n' ||
                       '$body$;')
    ;
    END
$$;

alter function it.function_get_create(text, text, text) owner to dvandenbosch;