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@xxxxxxxx> wrote in message news:hl11bd$2fs3$1@xxxxxxxxxxxxxxx > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
