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

.........


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

psql-novice@netzach.co.il wrote:
>
> Imagine a database-within-a-database implementation like this:

That is essentially how PG is already written, except they've removed
the need for us programmers to use SQL through the virtualization
layers.  Or rather, the SQL is the base implementation and there are
then system catalogs and system view which allow access to system
metadata.  All you're really doing here is forcing users to go through
layers of metadata.  Why is this better than creating actual relations
(tables)?

All you're doing is piling metadata on top of metadata.  Unless the
objects you're interested in are databases themselves, this serves
little purpose.  A database of databases ultimately breaks first normal
form, since by definition you're entering discrete elements into a
single field.

You want a generic record-returning function?  Here: SELECT

> 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 ?

Not sure if I'm reading you right here, but I believe you told it what
to expect when you said:
"CREATE FUNCTION vtable(text) RETURNS record"

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of
psql-novice@netzach.co.il
Sent: Tuesday, January 16, 2007 5:27 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Question about the need to specify column column
definition list for functions returning "record"

.........


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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster