Imagine a table in Postgres:
Firstname Surname Age
------------------------------
Joe Bloggs 5
Sam Bloggs 7
Ellie Jones 4
Mike Smith 10
I would like to range-filter this, based on an array of pairs of values (tuples):
{Surname=Bloggs && Age>=6 },
{Surname=Smith && Age>=10}
To return:
Firstname Surname Age
------------------------------
Sam Bloggs 7
Mike Smith 10
I realise I can do this by hand-rolling a SQL statement like this:
SELECT * FROM MyTable t
WHERE (t.Surname = 'Bloggs' AND t.Age >= 6 )
OR (t.Surname = 'Smith' AND t.Age >= 10)
However, I need to call this from C#, and I'm interested in solutions which avoid having to generate a plain-text SQL statement for every query.
Is it possible to do this with a 'generic' SQL statement, passing in some kind of array of tuples / composite types as a filter parameter?
In other RDBMS I could, for example, populate a temporary table with the pairs of values, and join on that table; or use a table-valued-parameter (in SQL Server). Is there an equivalent in Postgres + NpgSql?
PS: I read in this question that using temporary tables for this may not be best practice in Postgres
The best solution we found was to pass in array of custom types, which you can then unnest into tabular form in the query, and join on to.
CREATE TYPE predicate_type AS (
Surname text,
Age int);
SELECT * FROM MyTable t
JOIN unnest('{"(Bloggs, 6)","(Smith, 10)"}'::predicate_type[]) p(Surname, Age)
ON t.Surname = p.Surname AND t.Age >= p.Age
I've defined the array argument literally here, but you can pass them in as parameters to your query.
For example, you can map an equivalent C# type to the Postgres type in Npgsql, then just pass a C# array of those types into your command as a parameter:
https://www.npgsql.org/doc/types/enums_and_composites.html#mapping-your-clr-types