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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
