Re: void function and view select

Thanks a lot for this answer.

Am 07.05.2018 um 16:06 schrieb David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>:

On Mon, May 7, 2018 at 6:52 AM, Philipp Kraus <philipp(dot)kraus(at)tu-clausthal(dot)de<mailto:philipp(dot)kraus(at)tu-clausthal(dot)de>> wrote:
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).

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.

Based on this http://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
but I didn’t find a working solution for my problem at the moment

Phil