Skip site navigation (1) Skip section navigation (2)

Question about the need to specify column column definition list for functions returning "record"

From: psql-novice(at)netzach(dot)co(dot)il
To: pgsql-novice(at)postgresql(dot)org
Subject: Question about the need to specify column column definition list for functions returning "record"
Date: 2007-01-16 22:26:39
Message-ID: Pine.LNX.4.58.0701170022540.26935@localhost (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-novice by date

Next:From: Brandon AikenDate: 2007-01-17 00:13:08
Subject: Re: Question about the need to specify column column definition list for functions returning "record"
Previous:From: Brandon AikenDate: 2007-01-16 21:50:22
Subject: Re: Problems with SSL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group