Обсуждение: 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