Thread: What's wrong with this function - "returns setof"

I can't figure out what's wrong with the syntax of this select
statments.

I have a view: create view test_view as select field1, field2                                        from table1,
table2                                      where ...;
 

And 3 functions:

create function test_func(varchar)
returns setof test_view '  select *  from test_view  where (complicated where clause);
' language 'sql';

create function get_field1(test_view)
returns vachar as '   select $1.field1;
' language 'sql';

create function get_field2(test_view)
returns numeric as '   select $1.field2;
' language 'sql';

I am trying to execute all of this as follows:

select get_field1(results.a), get_field2(results.b)
from (select test_func('aaa') as p) as resutls;

I am getting the following error:
ERROR:  No such attribute or function 'get_field1'

But when I do it this way:
select field1(test_func('aaa')), field2(test_func('aaa'));

it works just fine.


The reason I am trying to do it the first way is that if I do it the
second way, function test_funs executes as many times as many columns
I need to select. Since I am using it for big reports it's a HUGE
performance problem. If I do it the second way, I execute test_func
only once and then use result set to get all the columns I need

Please help me out

Thanks in advance

alla@sergey.com (Alla) writes:
> select get_field1(results.a), get_field2(results.b)
> from (select test_func('aaa') as p) as resutls;

> I am getting the following error:
> ERROR:  No such attribute or function 'get_field1'

Well, correct syntax would be
select get_field1(results.p), get_field2(results.p)from (select test_func('aaa') as p) as results;

but unfortunately that still isn't gonna work.  The code supporting
functions returning sets is fairly disheveled at the moment, having
suffered a lot of bit-rot and no attention since Berkeley days; and
even back then there seems to have been some fatal confusion between
true functions and "set attribute" functions.  Here the parser is
mistaking results.p for a "set attribute", which it ain't.

We are talking about resurrecting the capability in a more
straightforward form, wherein you'd write just
select field1, field2 from test_func('foo') as results;

ie, the result of a function returning setof would be treated
syntactically as a table in its own right; but it's not happening
quite yet.
        regards, tom lane