Search Postgresql Archives

Re: void function and view select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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).

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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux