On Mon, Jan 09, 2006 at 01:01:33PM -0800, Matthew Peter wrote: > One other quick question, (figure it still applies to the subject > line :) when returning a row from a function I'm trying to include an > aggregate, but it's not showing up in the query result and I think > it's because it's not included in the RETURN NEXT row;? How do I > return it as part of the resultset... Terminology point: you used the word "aggregate" but the function below doesn't have an aggregate. Aggregates are functions that operate on multiple rows, like count() and sum(); substr() doesn't do that so it's not an aggregate. > create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$ > DECLARE > row my_tbl%rowtype; > > BEGIN > FOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tbl [...] You've declared the row variable to be of type my_tbl so whatever columns my_tbl has are the columns you get. If you want to return additional columns then you have a few choices: 1. Create a composite type with the desired columns, declare the function to return SETOF that type, and declare row to be of that type. 2. Declare the function to return SETOF record, declare row to be of type record, and provide a column definition list when you call the function. 3. Use OUT parameters (new in 8.1). -- Michael Fuhr
