Parametrized CTE

user_651174 0
Viewed 42

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

postgresql sql
1 Answers

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.

user_19957380
Related