Re: plpgsql: return multiple result sets or temp table - Mailing list pgsql-general

Jeff,

Thank you for the idea about cursor unfortunately it's not a right solution in
my situation.

I think I didn't explain properly what my problem is.
It's obvious that Eng is not my native language:)

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 ?


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