Re: void function and view select

On Mon, May 7, 2018 at 6:52 AM, Philipp Kraus <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).

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.