Postgres PLPGSQL 'un-stored' procedures?

Question

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.

Hmm, looks like this is working as intended - DO blocks cannot return rows. :( dba.stackexchange.com/questions/25075/… — Kyle
– Kyle, Commented Jul 1, 2020 at 18:54

Kyle · Accepted Answer · 2020-07-01 21:13:41Z

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.......

answered Jul 1, 2020 at 21:13

Kyle

1112 bronze badges

Add a comment |

CL. · Accepted Answer · 2020-07-03 12:46:30Z

1

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;

answered Jul 3, 2020 at 12:46

CL.

5,3451 gold badge23 silver badges23 bronze badges

Add a comment |

user1822 · Accepted Answer · 2020-07-03 13:42:04Z

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.

Stack Exchange Network

Postgres PLPGSQL 'un-stored' procedures?

3 Answers 3

Linked

Hot Network Questions

Postgres PLPGSQL 'un-stored' procedures?

3 Answers 3

Linked

Related

Hot Network Questions