Re: plpgsql question

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