Search Postgresql Archives

Re: plpgsql question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux