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

"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