FUNCTION BASE CON TABLAS TEMPORALES

CREATE OR REPLACE FUNCTION function_base_con_tablas_temporales() RETURNS jsonb LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE --CONSTANTES __MSJ_ERROR CONSTANT CHARACTER VARYING DEFAULT 'Hubo un error'; __GRUPO_PARENTESCO CONSTANT INTEGER DEFAULT 5; __STATUS_SUCCESS CONSTANT INTEGER DEFAULT 0; __STATUS_ERROR CONSTANT INTEGER DEFAULT 1; __STATUS_ERROR_OTHERS CONSTANT INTEGER DEFAULT 2; --VARIABLES __v_result JSONB; __v_msj_excep TEXT; BEGIN CREATE TEMP TABLE temp_table ON COMMIT DROP AS SELECT 1 AS valor; __v_result := JSONB_BUILD_OBJECT( 'status', __STATUS_SUCCESS, 'data', (SELECT JSONB_AGG(row_to_json(tab)) FROM temp_table tab) ); RETURN __v_result; EXCEPTION WHEN SQLSTATE 'SMILE' THEN __v_result = JSONB_BUILD_OBJECT('status', __STATUS_ERROR, 'msj' , SQLERRM); RETURN __v_result; WHEN OTHERS THEN GET STACKED DIAGNOSTICS __v_msj_excep = PG_EXCEPTION_CONTEXT; __v_result = JSONB_BUILD_OBJECT('status', __STATUS_ERROR_OTHERS , 'msj' , __MSJ_ERROR, 'stack_error', CONCAT(SQLERRM, ' - ', __v_msj_excep)); RETURN __v_result; END; $BODY$;