Thread: Functions that Return A Record

I am using version 8.0 on a Windows 2003 platform.

I am getting the following error message when trying to return a record:
    ERROR:  a column definition list is required for functions returning
"record"

My original query was to a function, and looked like this:
   select * from SnmpNodeVersion(4);
Figuring it needs to see a list of columns, I enumerated what I expected to
come back:
  select node_id, version, snmp_timeout, snmp_retries, snmp_community_ro,
snmp_community_rw,
  snmp_user_name, snmp_authentication_type, snmp_auth_password,
snmp_privacy_type,
  snmp_privacy_password from SnmpNodeVersion(4);
That didn't work, either; same error. I was thinking that it would be able
to get the
column defintions through the views I am using.

Re-read section 35.7.1.1 on the documentation manual having to do with
'RETURN expression':
   "To return as composite (row) value, you must write a record or row
variable as
    an expression"
The variable being returned was declared as a ROW variable, on a view:
   snmp_rcd_v3    node_snmp_v3_properties_v%rowtype;
My RETURN statements look like this:
   return snmp_rcd_v3;

Information I have found on Google has not helped. Anybody got any ideas? I
attached my function and view. Thanks for whatever insight you can give.

On Tue, 26 Jul 2005, Lane Van Ingen wrote:

> I am using version 8.0 on a Windows 2003 platform.
>
> I am getting the following error message when trying to return a record:
>     ERROR:  a column definition list is required for functions returning
> "record"
>
> My original query was to a function, and looked like this:
>    select * from SnmpNodeVersion(4);
> Figuring it needs to see a list of columns, I enumerated what I expected to
> come back:
>   select node_id, version, snmp_timeout, snmp_retries, snmp_community_ro,
> snmp_community_rw,
>   snmp_user_name, snmp_authentication_type, snmp_auth_password,
> snmp_privacy_type,
>   snmp_privacy_password from SnmpNodeVersion(4);
> That didn't work, either; same error. I was thinking that it would be able
> to get the
> column defintions through the views I am using.

If the function returns setof record, IIRC what you need to say is:
from SnmpNodeVersion(4) AS tab(node_id int, ...)

However, I'd suggest either defining the function to return the correct
view type, or, since there are two, making a composite type of the
appropriate structure and having the function return that.