Let's say I have a table-function that I'm currently doing with a CTE, for example:
WITH info AS (
SELECT * FROM sales WHERE website='Google'
) SELECT * FROM info JOIN other USING (id)
Is there a way to parametrize the CTE such that I can do something like:
WITH table_function_info($var1) AS (
SELECT * FROM sales WHERE website=$var1
)
And then I could call it as:
WITH info AS
table_function_info('Yahoo')
SELECT * FROM info JOIN other USING (id)
Or what is the proper way to parametrize a table function in a postgres (CTE?) Note that I don't necessarily know the output columns, and they may potentially change (for example, if a user adds/removes a column in the sales
table).
CodePudding user response:
Create a function that returns a set of rows of the type sales
, e.g.
create or replace function select_from_sales(arg text)
returns setof sales language sql as $$
select * from sales where website = arg
$$;
The output format of the function will adapt to possible modifications to the table structure, test it in Db<>fiddle. Postgres automatically creates a corresponding type for every created/altered table in a database. This allows a user to declare the return type of a function in an easy way.