Re: plpgsql: return multiple result sets or temp table

--- Oksana Yasynska <oksana(at)athabascau(dot)ca> wrote:

> I need to write a plpgsql function which returns
> information selected from the
> 50 tables (in the following example: title,
> descriptions and locations) to
> the other application. By the way, data has a tree
> structure.
>
> I have an idea to use function to build a temp
> table.
> Is it feasible to return temporary table as a
> plpgsql function result ?

Yes. This was a commonly used workaround before
version 7.2.x. A couple of points to be aware of:
* A temporary table persists only for (and is only
available to) the current user session.
* Better to use "EXECUTE" to create the table, as
there are some potential gotchas with tables created
by a function. Check the archives for plenty of
examples.

>
>
> For example, 3 tables:
> CREATE TABLE "lom" (
> "id" int4 DEFAULT nextval('"lom_id_seq"'::text)
> NOT NULL,
> "title" varchar(1000));
>
> CREATE TABLE "description" (
> "id" int4 DEFAULT
> nextval('"description_id_seq"'::text) NOT NULL,
> "lom_id" int4,
> "description" varchar(2000));
>
> CREATE TABLE "location" (
> "id" int4 DEFAULT
> nextval('"location_id_seq"'::text) NOT NULL,
> "lom_id" int4,
> "uri" varchar(1000));
>
> With the following information:
>
> INSERT INTO "lom" ("id", "title") VALUES(948,
> 'title');
>
> INSERT INTO "description" ("id", "lom_id",
> "description") VALUES(564, 948,
> 'description1');
> INSERT INTO "description" ("id", "lom_id",
> "description") VALUES(565, 948,
> 'description2');
>
> INSERT INTO "location" ("id", "lom_id", "uri")
> VALUES(1258, 948,
> 'http://yahoo.ca - location1');
> INSERT INTO "location" ("id", "lom_id", "uri")
> VALUES(1259, 948,
> 'http://google.ca - location2');
>
>
>
> Oksana
>

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com