Parameterized queries with table parameters
How can I build a parameterized query / view / stored procedure in PostgreSQL (or, reluctantly, a function in Java) to support table-valued parameters?
My current toolchain is PostgreSQL, JDBC and Java with the jooq library.
In some context.
I have a (quite complex actually) query that defines some aggregation processing for some statistics. For simplicity, let's assume it looks like this:
create view aggregated_data as
select table1.c1, sum(table2.c2)
from table1
inner join table2 on table1.c1 = table2.c1
group by table1.c1;
What I want to do is to parameterize this definition so that I can pass "table2" as a parameter because I want "table2" to only pass rows based on other conditions.
More specifically, I am behind something like:
create view aggregated_data(some_table_valued_parameter) as
select table1.c1, sum(table2.c2)
from table1
inner join (
select c1, c2 from some_table_valued_parameter
) table2 on table1.c1 = table2.c1
group by table1.c1;
How can I define something that allows me to keep all forests and just go to some_table_valued_parameter?
I am considering some kind of stored procedure as my first option, although from what I understand PostgreSQL does not support table value parameters, so this might not be possible.
The second option (which I'm sure will work) is to use JOOQ and a Java function to build the query dynamically. This is less preferable because the database is designed to support console logins and SQL input, and I would like them to be able to use forests as well.
Any hints or tips would be appreciated.
source to share
It seems like your main problem is to have a parameter stored in the table that is really not supported in PostgreSQL. But you can use an array of records, which is essentially the same as a bit bigger than a slab (and possibly some performance implications):
CREATE TYPE my_rec AS (
c1 BIGINT,
c2 BIGINT
);
And then:
CREATE FUNCTION my_func(almost_a_table my_rec[])
RETURNS TABLE (c1 BIGINT, c2 BIGINT)
AS $$
BEGIN
RETURN QUERY
SELECT table1.c1, sum(table2.c2)
FROM table1
INNER JOIN (
SELECT u.c1, u.c2 FROM unnest(almost_a_table) u
) table2 ON table1.c1 = table2.c1
GROUP BY table1.c1;
END
$$ LANGUAGE plpgsql;
How to use this feature:
SELECT *
FROM my_func((
SELECT array_agg(row(t.a, t.b)::my_rec)
FROM some_table t
))
Using jOOQ for the above
Note that since you mentioned that you have jOOQ in your stack, this approach may be ideal when using jOOQ as well, since the jOOQ code generator generates all the boilerplate code for you, so you can access my_func
from your queries jOOQ are the same. Details here:
http://www.jooq.org/doc/latest/manual/sql-building/table-expressions/table-valued-functions
source to share
You cannot do this in a view, but you can create a PL / pgSQL function that takes a table or view name and returns records that match the query definition. Since the table name is unknown beforehand, you need to dynamically execute the query on the table name passed with RETURN QUERY EXECUTE
:
CREATE FUNCTION aggregated_data(nm name) RETURNS TABLE (c1 integer, c2 float) AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT table1.c1, sum(table2.c2) ' ||
'FROM table1 ' ||
'INNER JOIN ( ' ||
' SELECT c1, c2 FROM ' || quote_ident(nm) ||
') table2 ON table2.c1 = table1.c1 ' ||
'GROUP BY table1.c1';
END; $$ LANGUAGE plpgsql STRICT;
Since this function returns a relation, you can - and indeed should - use this function in the clause of FROM
your larger query, just as you would with a view:
SELECT t.*, ad.c2
FROM t
JOIN aggregated_data('relation_with_c1_and_c2_columns') ad ON ad.c1 = t.id;
source to share