Re: void function and view select

[Philipp Kraus <philipp.kraus@xxxxxxxxxxxxxxx>]

Thanks a lot for this answer.

Am 07.05.2018 um 16:06 schrieb David G. Johnston <david.g.johnston@xxxxxxxxx>:

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