Re: "parameterized views" or return-type-inferring SQL functions?

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

Oh, sorry for the reply-to-self, but I know I can write an SQL function
with an OUT parameter list to do this. Like creating a custom rowtype
for the job, though, this gets cumbersome if the column-list is long, or
changes to the input tables might ever result in a change to column-list
types, add columns, etc.

Being able to write:

CREATE OR REPLACE FUNCTION blah(IN DATE, OUT a.*, OUT b.*) AS $$
SELECT * FROM a, b;
$$ LANGUAGE 'sql'

... would be somewhat helpful as another form of the ability to expand
rowtypes for use in declarations, but having to list all the OUT
parameters explicitly as I currently do is no better than using CREATE
TYPE to make a custom rowtype or listing all the colums at call-sites
for a RECORD returning function.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/