0

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.

1

3 Answers 3

1

I have found this answer within the great dba.stackexchange.com resource that appears to show that I'm not going to be able to get a return value out of a DO block:

Running a CTE query in a loop using PL/pgSQL

Will leave this open for a bit to see if anyone has a cool workaround.......

0

A DO block is the same as a temporary function that returns void. If you cannot create your own temporary function (not even in pg_temp), then you have to write a single query for all values:

-- two columns
SELECT (SELECT count(*) FROM mycustomer1.accounts_user) AS count1,
       (SELECT count(*) FROM mycustomer2.accounts_user) AS count2;
-- two rows
SELECT 'mycustomer1' AS tenant, count(*) FROM mycustomer1.accounts_user
UNION ALL
SELECT 'mycustomer2' AS tenant, count(*) FROM mycustomer2.accounts_user;
0

The only way to run dynamic SQL without PL/pgSQL is through query_to_xml().

If this is only about getting the row counts for multiple tables in multiple schemas, you can use that:

with input (tenant) as (
  -- your list of tenants
  -- this could be extended to make the table names dynamic as well
  values 
     ('mycustomer1'), ('mycustomer2')
), counts as ( 
  select tenant, 
         query_to_xml(format('select count(*) from %I.%I', tenant, 'accounts_user'), true, true, '') as cnt
  from input
)
select xc.tenant, c.*
from counts xc
  cross join xmltable ('/row/count' 
                       passing xc.cnt 
                       columns row_count int path '.') as c;

The major difference to your expected DO block result is, that each count is a separate row, rather than a column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.