Re: Function error- A column definition list is required for functions returning "record"

Hi Tom,
thanks for your reply. Just a follow up question about functions: would it
return the result if some of the parameters are null?

Cheers,
Ben

On 8/18/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "ben sewell" <mosherben(at)gmail(dot)com> writes:
> > I have this error when I tried testing my function. I set the parameters
> to
> > 18,2,,,,,,,, as pandctype but it said this error: A column definition
> list
> > is required for functions returning "record". I got my UDF setup but I
> don't
> > see what the problem is. Can anyone shed any ideas on this?
>
> > create or replace function reports (inreport_id integer,inadviser_id
> integer,inprovider_id integer,inintroducer_id integer,
> > inplangroup_id integer,inplantype_id integer,indatespecific_start
> date,indatespecific_end date,inchild24 date,inchild26 date)
> > returns record as'
>
> You can't simply declare the function as "returns record", because the
> calling query has no idea what to expect (the parser needs to know
> what columns will come out of the function call). There are basically
> two approaches you can use:
>
> 1. Declare the function as returning a specific rowtype. Either create
> a named composite type and declare the function as returning that, or
> (if you're using PG 8.1 or later) declare the function with some OUT
> parameters to show what columns it returns.
>
> 2. Declare the set of columns to be returned in the calling query, eg
>
> SELECT * FROM myfunc(...) AS foo(c1 int, c2 text, ...)
>
> This is what the error message is talking about. This case is mainly
> meant for special functions like dblink() that are designed to support
> query-determined column lists.
>
> You might want either of these depending on what you're doing, but
> the first is probably simpler.
>
> regards, tom lane
>