Re: Function that creates a custom table AND returns it = impossible in pg?

I am starting to see that this (and certainly my previous post about a
function as table-pointer) is impossible because of the pg planner. But
because the planner is required in every execution of a function, it puts
the constraint that a function cannot determine it's output by itself, but
must obey the one predefined, or (in case of SETOF RECORDS) known by the
initializer. The last, IMHO, is simply a bad workaround, not only because it
is "unmodular" (see infra), but also because it allows serieus coding
mistakes. (Suppose f() returns a table (men, women), and you redefine it in
your query to (women, men) and then put constrainsts on the men column: the
results would be untrue, and the bug hard to find.)

So because every function needs to have a predefined output, this constraint
severely limits the "PL" in pgSQL. In my own project, I wanted to write a
couple of fucntions with which the application would interact, instead of
writing sql in the application itself. pg apparently hardly makes this
possible.

I think there is a solution for this, which is implementing a keyword for a
more generic execution of a function that bypasses the planner and is
equivalent to SELECT * FROM f(). For example, EXECUTE FUNCTION
function_name()...

So... is there a grain of truth in all this?
Regards,
Davor

"Davor J." <DavorJ(at)live(dot)com> wrote in message
news:hl11bd$2fs3$1(at)news(dot)hub(dot)org(dot)(dot)(dot)
> What I want is something similar to this:
>
> CREATE OR REPLACE FUNCTION f( /* "some args..." */)
> RETURNS SETOF RECORD AS
> $BODY$
> DECLARE
> ...
> BEGIN
> DROP TABLE IF EXISTS tbl_temp;
>
> CREATE TEMPORARY TABLE tbl_temp(
> -- "based on args..."
> );
>
> WHILE
> INSERT INTO tbl_temp VALUES (/*"some values"*/);
> END LOOP;
>
> /*create indexes on it, manipulate, whatever...*/
>
> RETURN QUERY SELECT * FROM tbl_temp;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql'
>
> The point is: only the function knows the structure (i.e. rowtype) of the
> created table, not the initializer. The initializer is only supposed to
> supply the arguments, run"SELECT * FROM f(some args);" and fetch the
> results. Is this possible in Postgres??
>
> (And for those who insist: no, the intializer can not run SELECT * FROM
> f(some args) AS table("table structure...");" This would imply the
> initializer already knows what the function will do. It goes beyond the
> point of modularity: why not just skip the function then and let the
> initializer write the whole query itself?)
>
> Original post:
> http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-it-impossible-675540.html
>
> Kind regards,
> Davor
>