Function that creates a custom table AND returns it = impossible in pg?
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
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@live.com> wrote in message
news:hl11bd$2fs3$1@news.hub.org...
Show quoted text
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.htmlKind regards,
Davor