.........
Imagine a database-within-a-database implementation like this:
CREATE TABLE virtual_tables(
tableid serial PRIMARY KEY,
tablename varchar(50) UNIQUE NOT NULL
);
INSERT INTO virtual_tables SELECT 1,'mytable';
CREATE TABLE virtual_columns(
columnid serial PRIMARY KEY,
columnname varchar(50) NOT NULL,
tableid integer NOT NULL REFERENCES virtual_tables,
UNIQUE(columnname,tableid)
);
INSERT INTO virtual_columns SELECT 1,'name',1;
INSERT INTO virtual_columns SELECT 2,'telephone',1;
CREATE TABLE virtual_data(
dataid serial PRIMARY KEY,
tableid integer NOT NULL REFERENCES virtual_tables,
recordid integer NOT NULL,
columnid integer NOT NULL REFERENCES virtual_columns,
actualdata text
);
INSERT INTO virtual_data (tableid,recordid,columnid,actualdata)
VALUES (1, 1, 1, 'me');
INSERT INTO virtual_data (tableid,recordid,columnid,actualdata)
VALUES (1, 1, 2, '1-800-800-8000');
INSERT INTO virtual_data (tableid,recordid,columnid,actualdata)
VALUES (1, 2, 1, 'you');
INSERT INTO virtual_data (tableid,recordid,columnid,actualdata)
VALUES (1, 2, 2, '1-999-999-9999');
CREATE FUNCTION vtable(text) RETURNS record AS '
..... Return a virtual table with columns named according
to the contents of virtual_columns and content according
to what is in virtual_data, like this:
name | telephone
------+---------------
me | 1-800-800-8000
you | 1-999-999-9999
The use ? Well, your imagination is the limit, but rapid prototyping is
one of them. The problem ?
This:
SELECT * FROM vtable('mytable');
ERROR: a column definition list is required for functions returning record
I have read all the explanations as to why I get this error and I
understand them, however the whole thing seems rather bureacratic to me.
For example:
SELECT * FROM vtable('mytable') AS (nonsense wrongtype, junk randomtype);
ERROR: wrong record type supplied in RETURN NEXT
My question is this - if Postgres is so smart, and knows what the correct
record type should be in RETURN NEXT, so that it can give me this error,
then why does it insist on me telling it explicitly what to expect ?
In my virtual table example above, I do not know in advance the number,
type or name of the fields any more than Postgres does. The workaround I
have found is to write a trigger for virtual_tables and virtual_columns
that automatically generates the SQL every time something changes, and
uses this to create a view. The view of course must be dropped each time
the trigger runs, and so to prevent problems I must lock the entire DB
when I do so. This workaround effectively uses a view to reinvent
record-type functions...
Is there some good rational explanation for why implementing truly-dynamic
record-returning function capability in Postgres would be a Bad Thing ? It
seems to me to just be a party spoiler.
Thanks v much,
Daniel
