Why parameter does not work in PostgreSQL function in from clause's table name? [duplicate]

Question

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

Use Dynamic SQL, by assembling the SQL as a string.

The Impaler
– The Impaler

2021-12-10 01:43:30 +00:00
Commented Dec 10, 2021 at 1:43 — The Impaler
– The Impaler, Commented Dec 10, 2021 at 1:43

Ramin Faracov · Accepted Answer · 2021-12-10 03:13:34Z

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$
;

answered Dec 10, 2021 at 3:13

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

1 Comment

Bergi Over a year ago

Don't forget to properly escape the table name

Collectives™ on Stack Overflow

Why parameter does not work in PostgreSQL function in from clause's table name? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related