"parameterized views" or return-type-inferring SQL functions? - Mailing list pgsql-general

Hi folks

I've noticed a pattern in my SQL and am curious to know if others face
the same thing, and if so how they're handling it.

I often have the need to wrap up some complex query 'x' into a reusable
unit, so I don't copy it repeatly all over the place. This query often
requires one or more parameters that aren't simple WHERE clause filters,
so it's not useful to make it a regular view.

This query is a join between two or more tables, or has some other
result for which there is no rowtype already defined, so I can't just
wrap it in an SQL function that returns a particular predefined rowtype.

Essentially, what I want is the behaviour of a view, which has an
implicit/inferred row type, combined with the parameterization of a SQL
function. I'm after a sort of parameterized view, I guess.

Consider, as a simple example that I perhaps could express another way,
but kind of shows the point:

SELECT a.*, b.*
FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1');


I can't CREATE VIEW for that, as there's no way to provide the param $1,
and a WHERE clause filtering the view's results can't have the same
effect. I can't:

CREATE OR REPLACE FUNCTION ab_ondate(DATE) RETURNS SETOF RECORD AS $$
SELECT a.*, b.*
FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1');
$$ LANGUAGE 'sql';

.. without having to specify an explicit column-list wherever
"ab_ondate" is used, as even "SELECT * FROM ab_ondate(current_date)"
will fail with:

ERROR:  a column definition list is required for functions returning
"record"


As far as I can tell, my options are to use an SQL function that
'RETURNS RECORD' and go through the pain of defining the column-list
wherever the function is used, or CREATE TYPE to make a custom rowtype
for it to return, which I then have to maintain. Both these options are
far from ideal if the function wraps up a join on one or more other
tables, as I might want to alter the column-list or column types of
those tables at some later point.

So, I'm curious about how practical it'd be to infer the type for (at
least inline-able) SQL functions that return 'RECORD', avoiding the need
to declare the return column-list in that case.

In many cases even the ability to express a "join type" as a return
would help, eg:

CREATE OR REPLACE FUNCTION ab_ondate(DATE) RETURNS SETOF (A JOIN B)
AS $$
SELECT a.*, b.*
FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1');
$$ LANGUAGE 'sql';


.. or a way of dynamically "including" the column-list of one or more
rowtypes in the type specifier given when calling the query, eg:


SELECT * FROM ab_ondate(current_date) AS (a.*, b.*);



Thoughts? Ideas?

Anyone running into this regularly? Found any good solutions?

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general