[Feature] Return agate_table in dbt run-operation result

[acjh]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Propagate the return value of dbt run-operation in the result as agate_table, similar to dbt show from #7208.

Expected usage:

>>> from dbt.cli.main import dbtRunner
>>> dbt = dbtRunner()
>>> res = dbt.invoke(['run-operation', 'select_something_with_return', '--args', 'name: world'])
>>> res.result.results[0].agate_table.print_json()
[{"name": "hello, world"}]

Describe alternatives you've considered

Subclass RunOperationTask and register a custom command run-operation-with-results.
Attempt: https://stackoverflow.com/revisions/79138906/4
Problems:

• It is clunky to override the run method to pass res/execute_macro_result as agate_table=execute_macro_result when instantiating RunResult — since there is no precise point to override, a user has to copy the entire implementation and make small changes. Overriding this way deprives the user of benefiting from future improvements to this method.
• It is also clunky and necessary to patch process_run_result in RunResultsArtifact.from_execution_results to propagate agate_result from RunResult to RunResultOutput.

Who will this benefit?

Anyone writing a macro that has a return value.

Are you interested in contributing this feature?

Yes

Anything else?

Asked by another user: https://stackoverflow.com/questions/79126257/accessing-query-results-from-dbt-in-python-script