Function that creates a custom (temporary) table AND returns a pointer to it = impossible in pg?
What I want is something similar to this:
CREATE OR REPLACE FUNCTION f( /* "some args..." */)
RETURNS text 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 'tbl_temp'::text;
END;
$BODY$
LANGUAGE 'plpgsql'
Basically, what I want is a similar function f() that returns me a "pointer"
to the table which I can use in some query like this: SELECT * FROM
regclass(f()); Currently, this query only gives me one row 'tbl_temp'...,
but not what I want: "SELECT * FROM tbl_temp;" Can this be done in Postgres?
Original post:
http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-a-pointer-675539.html
Kind regards,
Davor
The impossibility of a function as a pointer seems clear to me: if it was
possible, it would switch off the planner. But it should not be the case.
For example: the planner could postpone planning if it detected a function
that returns a set, execute it and wait for the results, and then use the
results for planning of the outer query. This would imply a layered query
planning/execution.
I think this approach would seriously extend the possibilities of pg in a
rather "object-oriented" way. Or am I wrong?
Regards,
Davor
"Davor J." <DavorJ@live.com> wrote in message
news:hl1170$29jn$1@news.hub.org...
Show quoted text
What I want is something similar to this:
CREATE OR REPLACE FUNCTION f( /* "some args..." */)
RETURNS text 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 'tbl_temp'::text;
END;
$BODY$
LANGUAGE 'plpgsql'Basically, what I want is a similar function f() that returns me a
"pointer" to the table which I can use in some query like this: SELECT *
FROM regclass(f()); Currently, this query only gives me one row
'tbl_temp'..., but not what I want: "SELECT * FROM tbl_temp;" Can this be
done in Postgres?Original post:
http://forums.devshed.com/postgresql-help-21/function-that-creates-a-custom-table-and-returns-a-pointer-675539.htmlKind regards,
Davor
In response to Davor J. :
What I want is something similar to this:
Basically, what I want is a similar function f() that returns me a "pointer"
to the table which I can use in some query like this: SELECT * FROM
regclass(f()); Currently, this query only gives me one row 'tbl_temp'...,
but not what I want: "SELECT * FROM tbl_temp;" Can this be done in Postgres?
Take a look at
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Thank you for the link Andreas. It was helpfull, but still not quite what I
need.
Regards,
Davor
""A. Kretschmer"" <andreas.kretschmer@schollglas.com> wrote in message
news:20100212114635.GB25282@a-kretschmer.de...
Show quoted text
In response to Davor J. :
What I want is something similar to this:
Basically, what I want is a similar function f() that returns me a
"pointer"
to the table which I can use in some query like this: SELECT * FROM
regclass(f()); Currently, this query only gives me one row 'tbl_temp'...,
but not what I want: "SELECT * FROM tbl_temp;" Can this be done in
Postgres?Take a look at
http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general