Question 246969 in PostgreSQL Question posted in
The official documentation can be found here.

Convert dynamic T-SQL to dynamic PostgreSQL PL/SQL

I wrote the SQL Server stored procedure usp_getrowcounts, shown below, that dynamically gets a row count from each table, as tables come and go, and need the equivalent dynamically generated and executed PL/SQL to work in PostgreSQL.

However, working with PostgreSQL documentation I found no way to translate and create the equivalent in PL/SQL. How would this functionality be re-written to run against a PostgreSQL database, where the SQL Server tables are replicated and row counts are needed to audit against the SQL Server source to ensure replication is in sync?

create or alter proc usp_getrowcounts
as

set nocount on

declare @sql nvarchar(max) = N''

drop table if exists #tablerows

select  top 0 convert(nvarchar(200), '') tablename, convert(int, 0) rows
into    #tablerows

select  @sql += 'insert #tablerows select ''' + table_name + ''' tablename, count(0) rows from [' + table_schema + '].[' + table_name + '] (nolock);'
from    information_schema.tables 
where   table_schema = 'dbo'
and     table_type = 'base table'
and     left(table_name, 1) != '_'

exec sp_executesql @sql

select  *
from    #tablerows
order by
        tablename
go
Tags:

2

Answers


  1. 0

    You can do something like this (with apologies to plsql programmers):

    
CREATE OR REPLACE FUNCTION public.exec(
text)
RETURNS TABLE (cnt bigint)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN 
    RETURN QUERY EXECUTE $1 ; 
END 
$BODY$;


select *
from information_schema.tables t
left join lateral exec('select count(*) as cnt from ' || t.table_schema || '.' || t.table_name) x 
 on true
where     left(table_name, 1) != '_'
;

    The function executes passed dynamic sql and returns count, then you traverse the table schema and get the count.

    Login or Signup to reply.
  2. 0

    It’s not necessary to use a user defined stored procudure to get the row counts for an arbitrary set of tables. The following query will return the row counts for all tables in the public schema:

    SELECT t.table_schema, t.table_name, (XPATH('/table/row/num_rows/text()', n.xml))[1]::text::bigint AS rows
  FROM information_schema.tables t
  CROSS JOIN LATERAL (SELECT QUERY_TO_XML(FORMAT($$SELECT COUNT(*) AS num_rows FROM %1$I.%2$I;$$,
                                                 t.table_schema,
                                                 t.table_name),
                                          FALSE,
                                          FALSE,
                                          '')) n(xml)
  WHERE t.table_type = 'BASE TABLE'
    AND t.table_schema = 'public'
  ORDER BY t.table_schema, t.table_name;

    This is more flexible than using a stored procedure because the query can be easily altered to restrict the set of tables using arbitrary criteria or gather additional statistics without having to modify any stored procedures. Using this function as the basis of a materialized view would effectively cache the results of the most recent run.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search