Обсуждение: void function and view select
Hello,
I have got a complex query with a dynamic column result e.g.:
select builddata('_foo‘);
select * from _foo;
The first is a plsql function which creates a temporary table, but the function returns void.
The second call returns all the data from this table. But the columns of the temporary table are
not strict fixed, so I cannot return a table by the function.
So my question is, how can I build with this two lines a view, so that I can run "select * from myFooView“ or
a function with a dynamic return set of columns e.g. „select myFoo()“?
Thanks
Phil
Hello,
I have got a complex query with a dynamic column result e.g.:
select builddata('_foo‘);
select * from _foo;
The first is a plsql function which creates a temporary table, but the function returns void.
The second call returns all the data from this table. But the columns of the temporary table are
not strict fixed, so I cannot return a table by the function.
So my question is, how can I build with this two lines a view, so that I can run "select * from myFooView“ or
a function with a dynamic return set of columns e.g. „select myFoo()“?
Executed queries must have a well-defined column structure at parse/plan-time, execution cannot change the columns that are returned.
By extension, a view's column structure must be stable. Writing:
CREATE VIEW v1 AS
SELECT * FROM tbl1;
Causes the view to defined with all columns of tbl1 as known at the time of the view's creation (i.e., * is expanded immediately).
You might be able to use cursors to accomplish whatever bigger goal you are working toward (I'm not particularly fluent with this technique).
The more direct way to accomplish this is:
SELECT *
FROM func_call() AS (col1 text, col2 int, col3 date)
i.e., have the function return "SETOF record" and then specify the format of the returned record when calling the function.
David J.
Thanks a lot for this answer.
Am 07.05.2018 um 16:06 schrieb David G. Johnston <david.g.johnston@gmail.com>:Hello,
I have got a complex query with a dynamic column result e.g.:
select builddata('_foo‘);
select * from _foo;
The first is a plsql function which creates a temporary table, but the function returns void.
The second call returns all the data from this table. But the columns of the temporary table are
not strict fixed, so I cannot return a table by the function.
So my question is, how can I build with this two lines a view, so that I can run "select * from myFooView“ or
a function with a dynamic return set of columns e.g. „select myFoo()“?Executed queries must have a well-defined column structure at parse/plan-time, execution cannot change the columns that are returned.By extension, a view's column structure must be stable. Writing:CREATE VIEW v1 ASSELECT * FROM tbl1;Causes the view to defined with all columns of tbl1 as known at the time of the view's creation (i.e., * is expanded immediately).
In my case this strict definition is not given, the column number and column types are not strict fixed, so based on this a view is not the correct tool.
You might be able to use cursors to accomplish whatever bigger goal you are working toward (I'm not particularly fluent with this technique).The more direct way to accomplish this is:SELECT *FROM func_call() AS (col1 text, col2 int, col3 date)i.e., have the function return "SETOF record" and then specify the format of the returned record when calling the function.
but I didn’t find a working solution for my problem at the moment
Phil