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?
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 ...
... table name (type text or name) - especially when tables may not actually exist (yet):
... object identifier (type regclass):
... row type of the table (type anyelement) using the concept of polymorphism:
Typically, you end up using dynamic SQL in the function. Be wary of SQL injection vectors. Related:
The return type depends on your input and what you want to achieve ...
You can't really do something like that because every function must either
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.
returns voidand a syntactically incorrectSELECTdon't make sense. And always your version of Postgres. I think there is a solution.