Postgres - SQL state: 22004 in EXECUTE function

Question

I'm trying to make a function to count the number of rows in a column. The objective is pass the table and the column as a parameter in a function and return the number of rows.

The function is:

CREATE OR REPLACE FUNCTION public.totalrecords(column_names text,table_names text)
    RETURNS bigint
    LANGUAGE 'plpgsql'

AS $BODY$DECLARE 
total integer;
BEGIN
    EXECUTE format('SELECT count(%s) into %I FROM %s', column_names,total,table_names);
    RETURN total;
END;$BODY$;

know anyone why doesn`t work? Any help is highly appreciated. Thanks in advance

"*count the number of rows in a column*" - columns don't have rows. Tables do. — a_horse_with_no_name, Feb 21 '20 at 09:00
You don't actually need a function for that: https://stackoverflow.com/a/38684225/330315 — a_horse_with_no_name, Feb 21 '20 at 09:01

Jim Jones · Accepted Answer · 2020-02-22T16:45:48.097

I assume it is only an exercise of plpgsql - as pointed out by @a_horse_with_no_name, you do not need a function for this. That being said, just put the INTO total outside the format(...) and you'll be fine. Also, there is no need to pass a column name to get a count of a table, so you can restrict your function to a single parameter, e.g.

CREATE OR REPLACE FUNCTION public.totalrecords(table_names text)
RETURNS bigint LANGUAGE 'plpgsql'
AS $BODY$
DECLARE total INTEGER;
BEGIN
 EXECUTE format('SELECT count(*) FROM %s', table_names) INTO total;
 RETURN total;
END;$BODY$;

Example:

CREATE TEMPORARY TABLE t (txt TEXT);
INSERT INTO t VALUES ('foo'),('bar');

SELECT * FROM totalrecords('t');


 totalrecords 
--------------
            2
(1 Zeile)

Postgres - SQL state: 22004 in EXECUTE function

1 Answers1