Postgres declare table as a parameter for a function?

Question

I would like to create a function that operates on a table, for example

create or replace function test(t table) 
    returns void language plpgsql as
$func$
begin   
    select * from t limit 10;
end;
$func$

Then, I can call the function with any tablename, e.g.

select test(myTable);

How would I do something like this?

Answer 1

You cannot declare a table as function parameter per se, since there are no table variables in Postgres. But there are various ways to achieve what you might want to achieve - which isn't exactly clear yet.

You can "pass" a table as ...

1. ... table name (type text or name) - especially when tables may not actually exist (yet):

   • Define table and column names as arguments in a plpgsql function?

2. ... object identifier (type regclass):

   • PostgreSQL: Pass table as argument in function
   • Table name as a PostgreSQL function parameter

3. ... row type of the table (type anyelement) using the concept of polymorphism:

   • Refactor a PL/pgSQL function to return the output of various SELECT queries

Typically, you end up using dynamic SQL in the function. Be wary of SQL injection vectors. Related:

• SQL injection in Postgres functions vs prepared queries
• PL/pgSQL regclass quoting of table named like keyword

The return type depends on your input and what you want to achieve ...

Answer 2

You can't really do something like that because every function must either

• Be statically typed
• Be declared with a table-alias specifying the types in the query.

So you'll either have to do..

CREATE FUNCTION test(t table) 
RETURNS composite-type
AS
  ...

Or,

SELECT *
FROM test(myTable) AS (x int, y int);

But at some point you have to state the types. Alternatively you can return a schema-less type, like jsonb.