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