how to create function in postgres that returns rowcounts between tables given as parameters

Question

I am having trouble returning the expected value using the below function in postgres and I am unsure what I am doing wrong.

function:

create or replace function check_row_diff(_table_minuend varchar(255), _table_subtrahend varchar(255))
returns bigint as $$
declare 
    v_num numeric;
BEGIN
    execute format ('SELECT
        (select count(*) from %s)
      - (select count(*) from %s) AS Difference', _table_minuend, _table_subtrahend);
return v_num;
end;
$$ language plpgsql;

then running a select, returns "NULL"

select check_row_diff('table_1', 'table_2');

using a "perform" statement just returns an error. running this as an SQL query returns the correct results. Table_1 (name obfuscated) has 4,568 records and table_2 (name obfuscated) has 2,284 records:

select 
(select count(*) from table_1)
- (select count(*) from table_2) as difference

returns a count of 2,284. I need this check to work because I am coding in python a middleware application that allows our systems to integrate to a cloud SaaS. I am using "working" tables and "golden" tables to ensure the data feed everyday is successful and records are not missing as this will integrate to many internal systems. I am not able to use UPSERT/MERGE in the version of postgres as the DB platform is SaaS cloud hosted in a serverless design (third party managed underlying instance). this function will be called as part of the data validation module prior to updating the "golden" tables and then delivering the data feeds to downstream systems.

please let me know if you need more information on the issue.

klin · Accepted Answer · 2018-03-25 16:29:02Z

1

The result of execute() should be assigned to the variable using into:

create or replace function check_row_diff(_table_minuend text, _table_subtrahend text)
returns bigint as $$
declare 
    v_num numeric;
begin
    execute format ('select
        (select count(*) from %s)
      - (select count(*) from %s)', _table_minuend, _table_subtrahend)
    into v_num;
    return v_num;
end;
$$ language plpgsql;

answered Mar 25, 2018 at 16:29

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hiker86 Over a year ago

awesome thank you! I think I had tried using into and had an errant semi-colon so I thought it was incorrect syntax. works perfectly!

Collectives™ on Stack Overflow

how to create function in postgres that returns rowcounts between tables given as parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related