0

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?

  • Please provide a meaningful example. returns void and a syntactically incorrect SELECT don't make sense. And always your version of Postgres. I think there is a solution. – Erwin Brandstetter Apr 24 '18 at 14:34
  • You could pass an array of a record type – a_horse_with_no_name Apr 24 '18 at 14:58
2

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):

  2. object identifier (type regclass):

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

In most cases you will end up using dynamic SQL inside the function. Be wary of SQL injections vectors. Related:

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.