Question about the need to specify column column definition list for functions returning "record"
От | psql-novice@netzach.co.il |
---|---|
Тема | Question about the need to specify column column definition list for functions returning "record" |
Дата | |
Msg-id | Pine.LNX.4.58.0701170022540.26935@localhost обсуждение исходный текст |
Ответы |
Re: Question about the need to specify column column definition list for functions returning "record"
|
Список | pgsql-novice |
......... 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
В списке pgsql-novice по дате отправления: