I'm getting the following error when I run the query below:
ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL function inline_code_block line 12 at SQL statement
I don't want to discard the results though! I want to display them. What's happening here; is Postgres refusing to provide results from a block that isn't a UDF or stored procedure? Or some other syntaxy thing?
DO $$ declare tenant text; result1 integer; result2 integer; BEGIN tenant='mycustomer1'; EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result1; tenant='mycustomer2'; EXECUTE format('SELECT count(*) from ' || tenant || E'.accounts_user;') INTO result2; select result1, result2; END; $$ LANGUAGE plpgsql;
Background: I'm trying to create a set of dynamic queries that can get values from multiple PG schemas to return in a single report.
Context: I'm using Metabase to run the query, with a read only connection talking to RDS / Postgres. My plan is to store the queries as Metabase items. I was originally going to try creating a temp table to put my results in, but I'm not allowed to CREATE anything, including UDFs and stored procedures, hence my thinking of this as an un-stored procedure.