0

I need to use PostgreSQL function like below but it does not work. Any helps?! I need to make below function not "select count(*) from table_name"

create or replace function count_rows_of_table(table_name text)
returns numeric
language plpgsql
as
$$
declare
   count numeric;
begin
   select count(*) 
   into count
   from table_name;
   
   return count;
end;
$$;

When I use above function with below sql query it does not work.. Any ideas?

temp=# create or replace function count_rows_of_table(table_name text)
temp-# returns numeric
temp-# language plpgsql
temp-# as
temp-# $$
temp$# declare
temp$#    count numeric;
temp$# begin
temp$#    select count(*) 
temp$#    into count
temp$#    from table_name;
temp$#    
temp$#    return count;
temp$# end;
temp$# $$;
CREATE FUNCTION
temp=# 
temp=# 
temp=# select n.nspname as table_schema, c.relname as table_name, c.reltuples as rows, count_rows_of_table(c.relname)
temp-# from pg_class c join pg_namespace n on n.oid = c.relnamespace
temp-# where c.relkind = 'r' and n.nspname not in ('information_schema','pg_catalog')
temp-# order by c.reltuples desc;
ERROR:  relation "table_name" does not exist
LINE 2:                  from table_name
                              ^
QUERY:  select count(*) 
                 from table_name
CONTEXT:  PL/pgSQL function count_rows_of_table(text) line 5 at SQL statement
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
TWpower
  • 39
  • 3

1 Answers1

0

You can write this function only using dynamic SQL. Sample:

CREATE OR REPLACE FUNCTION count_rows_of_table(table_name text)
RETURNS numeric 
LANGUAGE plpgsql
AS $function$
declare
  v_count numeric;
  v_sql text; 
begin
    
    v_sql = 'select count(*) from ' || table_name; 
    execute v_sql into v_count;
   
    return v_count;

end;
$function$
;
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8