Обсуждение: Function Returning Table/Record

Hi,

I have a function that returns a subset of data from a specific table.
The function must return all the rows that are present in the parent
table.

Is there an easy way to create this function so that when the table
definition changes I don't need to change the function definition.

I know I can create the table to return RECORD but then when I call
the function I must specify the structure of the returned type.
Eg. SELECT * FROM my_func(args) AS foo(col type, ...);

There are a lot of columns so this is not a very practical solution.

Isn't there a shorter/better/easier way to do this?

--
Carel Combrink
s25291930@tuks.co.za

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.

"Carel Combrink" <s25291930@tuks.co.za> writes:
> I have a function that returns a subset of data from a specific table.
> The function must return all the rows that are present in the parent
> table.

> Is there an easy way to create this function so that when the table
> definition changes I don't need to change the function definition.
> I know I can create the table to return RECORD but then when I call
> the function I must specify the structure of the returned type.

Can't you declare it as returning the table's rowtype?

            regards, tom lane

Quoting "Tom Lane" <tgl@sss.pgh.pa.us>:

>
> "Carel Combrink" <s25291930@tuks.co.za> writes:
>> I have a function that returns a subset of data from a specific table.
>> The function must return all the rows that are present in the parent
>> table.
>
>> Is there an easy way to create this function so that when the table
>> definition changes I don't need to change the function definition.
>> I know I can create the table to return RECORD but then when I call
>> the function I must specify the structure of the returned type.
>
> Can't you declare it as returning the table's rowtype?
>
>             regards, tom lane
>

I have tried to do this with no success, using the ROWTYPE keyword.
After your response I have tried:
RETURNS SETOF table_name
And it correctly returns what I've wanted.

Thank you,
--
Carel Combrink
s25291930@tuks.co.za

This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.