I have created the following stored procedure using my IntelliJ SQL workbench:
create or replace procedure sp_get_internal_members(tmp_name inout varchar) as
$$
begin
execute 'drop table if exists ' || tmp_name;
execute 'create temp table ' || tmp_name || ' as select id
from member where ( client_id = ' || quote_literal('07d18096-b7d0-41a8-7gh4-4fd27649d2eb') ||
'or client_id = ' || quote_literal('d1b5f4b5-5dec-4h9v-9e4a-bcf835fcb2ad') ||
')';
end
$$ language plpgsql;
This works fine when I connect directly to Redshift and execute the procedure as such:
call sp_get_internal_members('internal');
select * from internal;
However, when I try to invoke the stored procedure in a Metabase question and query the result, I simply get a one-column, one-row result: internal
.
How can I make use of the result sets of invoked existing Redshift stored procedures in Metabase questions?