Passing table name to a function and returning a table

Question

I need to run the same query on many tables of a DB (PostgreSQL 9.3.5) and passing the table name as an argument to a function seems a logic thing to do. Reading at examples in the docs and numerous S.O. postings, I still cannot nail it down.

Using this minimal example:

CREATE TABLE films (
code        char(5),
kind        varchar(10)
);
INSERT INTO films VALUES
('UA502', 'Comedy');

And this function:

CREATE OR REPLACE FUNCTION foo(_t text)
RETURNS void AS  -- I know, void is not what I am after
$func$
BEGIN
   EXECUTE format('SELECT count(*) FROM %I', _t);
END
$func$  LANGUAGE plpgsql;

The following query catches something:

mydb=> SELECT foo('films');
 foo
-----
(1 row)

However because I want to retrieve a table I edit the second line and re-create the function like so:

CREATE OR REPLACE FUNCTION foo(_t text)
RETURNS TABLE(count integer) AS
$func$
BEGIN
   EXECUTE format('SELECT count(*) FROM %I', _t);
END
$func$  LANGUAGE plpgsql;

However the same query does not catch anything:

mydb=> SELECT foo('films');
foo
-----
(0 rows)

Could someone let me know what I am missing here and how to proceed? Any hints greatly appreciated!

Can you please try this? RETURN QUERY instead of EXECUTE format in Return Table query — Len, Mar 31 '17 at 02:52
The crucial thing hier is the RETURN type. If all you want is an integer column (the count), the task is simple. If you want to return rows of the input table it's a bit more sophisticated. — Erwin Brandstetter, Mar 31 '17 at 12:32
Thanks @Erwin, I'll keep that in mind as I will soon try to get there. But it was important for me to get some basic concept first. I had the feeling I could not even get to "Hello World!"... — jjap, Mar 31 '17 at 13:07
BTW, the question title contradicts the question, because you are ***not*** returning a table, just a value. Not a set-returning function, different on principal. — Erwin Brandstetter, Mar 31 '17 at 15:29

klin · Accepted Answer · 2017-03-31T03:37:19.073

If a function returns a scalar value it must contain return <expression>:

declare a variable,
assign a value to it (in the example with execute ... into ...),
use it in return <expression>.

Example:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns bigint as
$func$
declare
    res bigint;
begin
    execute format('select count(*) from %I', _t) into res;
    return res;
end
$func$ language plpgsql;

select foo('films');

If a function returns a table it must contain return next or return query:

assign a value to a column of declared return table,
use return next,
call the function in FROM clause, not in SELECT list.

Example 1:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns table(count bigint) as
$func$
begin
   execute format('select count(*) from %I', _t) into count;
   return next;
end
$func$  language plpgsql;

select * from foo('films');

If a function returns a table you can also use return query:

Example 2:

drop function if exists foo(_t text);
create or replace function foo(_t text)
returns table(count bigint) as
$func$
begin
    return query
        execute format('select count(*) from %I', _t);
end
$func$  language plpgsql;

select * from foo('films');

Thanks @klin. Searching for `return next` and `return query` also got me the right part of the docs: 40.6.1. Returning From a Function in plsql. For further reading... — jjap, Mar 31 '17 at 13:13

score 4 · Answer 2 · edited May 23 '17 at 10:30

You need dynamic SQL with EXECUTE and you need to escape identifiers properly. You already got that right.

You had problems with how to return values and @klin provides solutions for that.

To pass a table name I suggest to use a regclass parameter. And, for simplicity, an OUT parameter for the result:

CREATE OR REPLACE FUNCTION f_count_rows(_tbl regclass, OUT _ct bigint)
  RETURNS bigint AS
$func$
BEGIN
   EXECUTE 'SELECT count(*) FROM ' ||  _tbl INTO _ct;
END
$func$  LANGUAGE plpgsql;

This way ...

You don't need to escape the table name manually because that's built into the type cast.
Most importantly, you can pass schema-qualified table names like myschema.mytable which would fail if you pass that as text and escape it in the function body with quote_ident(_tbl) or format('... %I' _tbl). Would be interpreted as table name "myschema.mytable". You would have to pass schema and table name separately to make it work.
The OUT parameter is handy because it replaces a variable declaration plus a RETURN command. Otherwise equivalent. When the function reaches end of control the current state of OUT parameters is returned automatically.

Table name as a PostgreSQL function parameter

To be clear: a RETURN command is only one way of several to return a scalar value from a function. There are others like the one I demonstrate above, and more:

Can I make a plpgsql function return an integer without using a variable?

Very informative, this allows me to slowly start understanding these concepts seen in various post ++ — jjap, Mar 31 '17 at 18:57

Passing table name to a function and returning a table

2 Answers2