Created
July 8, 2020 13:45
-
-
Save danielleevandenbosch/292cd34b3de9f22137b75044dc7ffe64 to your computer and use it in GitHub Desktop.
create get functions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment