Re: function problems

On 11/16/2010 3:57 PM, Christine Penner wrote:
> I have seen other functions (written by others) that do this. So I
> assume its ok. I'm open to suggestions though. As long as it works.
>
> At 01:54 PM 16/11/2010, you wrote:
>> On 16/11/2010 21:27, Christine Penner wrote:
>>
>>> create or replace function SubjectDisplay(IN subj varchar,IN meetCode
>>> numeric,IN meetTrWP integer,OUT Display varchar)
>>>
>>> returns varchar as
>>
>> I'm not certain about this, but is it a mistake to mix OUT parameters
>> and RETURNS?
>>
>> Ray.
>>
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> rod(at)iol(dot)ie
>>

I have used OUT combined with "returns setof record", it makes the
result set have the columns specified as OUT params.

like:
create or replace function TotalCustProd( xsdate timestamp, xedate
timestamp,
out xcust varchar(100), out xcontract varchar(80), out xjob text, out
xtask text, out xparcels integer,
out xhours float, out xrate float, out xpclperhour varchar(1), out
xamount float
) RETURNS SETOF record AS $$

the result set would be xcust, xcontract, etc...

Newer versions of PG have "returns table" support:

create or replace function findBadRates(sdate date) returns table(rrowid
integer, rlookuprate decimal(5,2)) as $$

Do you want to return one single value, or a set of rows?

-Andy