Proposal: stand-alone composite types

3. Modify CREATE FUNCTION to allow the implicit creation of a dependent
composite type, e.g.:

CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] )
RETURNS [setof] { data_type | (column_name data_type [, ... ]) }...

This would automatically create a stand-alone composite type with a
system generated name for the function. Thanks to the new dependency
tracking, the implicit composite type would go away if the function
is dropped.

Comments, objections, or thoughts?

I'm just licking my lips in anticipation of converting my entire website to
SRFs ;)

Chris

Joe Conway wrote:

2. Borrow the needed parts from CREATE and DROP VIEW to implement a new
form of the CREATE TYPE command, with syntax something like:

CREATE TYPE typename AS ( column_name data_type [, ... ] )

This would add a pg_class entry of relkind 'c', and add a new
pg_type entry of typtype 'c', with typrelid pointing to the
pg_class entry. Essentially, this new stand-alone composite type
looks a lot like a view without any rules.

I'm working on stand-alone composite types and running into a
reduce/reduce problem with the grammer. Any suggestions would be
appreciated. Here's what I have:

DefineStmt:
CREATE AGGREGATE func_name definition
{
. . .
}
| CREATE TYPE_P qualified_name AS
'(' TableFuncElementList ')'
{
CompositeTypeStmt *n = makeNode(CompositeTypeStmt);
n->typevar = $3;
n->coldeflist = $6;
$$ = (Node *)n;
}

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:

I'm working on stand-alone composite types and running into a
reduce/reduce problem with the grammer. Any suggestions would be
appreciated. Here's what I have:

DefineStmt:
| CREATE TYPE_P qualified_name AS
'(' TableFuncElementList ')'

Use any_name, not qualified_name. As-is, you're forcing the parser
to try to distinguish the two forms of CREATE TYPE before it can
see anything that would tell the difference.

In hindsight I think it was a mistake to set up RangeVar/qualified_name
as a distinct reduction path from non-relation qualified names ---
we'd have been better off using a single production and a uniform
intermediate representation. But I haven't had time to investigate
simplifying the grammar that way.

regards, tom lane

Joe Conway wrote:

Based on Tom's suggestion, I propose the following:

1. Define a new pg_class relkind as 'c' for composite. Currently relkind
can be: 'S' sequence, 'i' index, 'r' relation, 's' special, 't'
toast, and 'v' view.

2. Borrow the needed parts from CREATE and DROP VIEW to implement a new
form of the CREATE TYPE command, with syntax something like:

CREATE TYPE typename AS ( column_name data_type [, ... ] )

This would add a pg_class entry of relkind 'c', and add a new
pg_type entry of typtype 'c', with typrelid pointing to the
pg_class entry. Essentially, this new stand-alone composite type
looks a lot like a view without any rules.

Items 1 and 2 from the proposal above are implemented in the attached
patch. I was able to get rid of the reduce/reduce conflict with Tom's
help (thanks Tom!).

test=# CREATE TYPE compfoo AS (f1 int, f2 int);
CREATE TYPE
test=# CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS 'SELECT fooid,
foosubid FROM foo' LANGUAGE SQL;
CREATE FUNCTION
test=# SELECT * FROM getfoo();
f1 | f2
----+----
1 | 1
1 | 2
(2 rows)

test=# DROP TYPE compfoo;
NOTICE: function getfoo() depends on type compfoo
ERROR: Cannot drop relation compfoo because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too
test=# DROP TYPE compfoo CASCADE;
NOTICE: Drop cascades to function getfoo()
DROP TYPE

Passes all regression tests (well, I'm on RedHat 7.3, so there are three
"expected" failures). Doc and regression adjustments included. If there
are no objections, please apply.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:

Items 1 and 2 from the proposal above are implemented in the attached
patch.

Good first cut, but...

I don't like the way you did the dependencies. In a normal relation,
the type row is internally dependent on the class row. This causes the
type to automatically go away if the relation is dropped, and it also
prevents an attempt to manually drop the type directly (without dropping
the relation). For a composite type, of course we want exactly the
opposite behavior: the class row should internally depend on the type,
not vice versa.

If you did it that way then you'd not need that ugly kluge in
RemoveType. What you'd need instead is some smarts (a kluge!?) in
setting up the dependency. Currently that dependency is made in
TypeCreate which doesn't know what sort of relation it's creating
a type for. Probably the best answer is to pull that particular
dependency out of TypeCreate, and make it (in the proper direction)
in AddNewRelationType.

Also, I'm not following the point of the separation between
DefineCompositeType and DefineCompositeTypeRelation; nor do I see a need
for a CommandCounterIncrement call in there.

You have missed a number of places where this new relkind ought to
be special-cased the same way RELKIND_VIEW is --- for example
CheckAttributeNames and AddNewAttributeTuples, since a composite type
presumably shouldn't have system columns associated. I'd counsel
looking at all references to RELKIND_VIEW to see which places also need
to check for RELKIND_COMPOSITE_TYPE.

regards, tom lane

Tom Lane wrote:

If you did it that way then you'd not need that ugly kluge in
RemoveType. What you'd need instead is some smarts (a kluge!?) in
setting up the dependency. Currently that dependency is made in
TypeCreate which doesn't know what sort of relation it's creating
a type for. Probably the best answer is to pull that particular
dependency out of TypeCreate, and make it (in the proper direction)
in AddNewRelationType.

OK -- I'll take a look.

Also, I'm not following the point of the separation between
DefineCompositeType and DefineCompositeTypeRelation; nor do I see a need
for a CommandCounterIncrement call in there.

Well the next thing I was going to work on after this was an implicitly
created composite type when creating a function. I thought maybe the
CommandCounterIncrement would be needed so that the type could be
created and then immediately used by the function. In any case, I'll
combine the two functions.

You have missed a number of places where this new relkind ought to
be special-cased the same way RELKIND_VIEW is --- for example
CheckAttributeNames and AddNewAttributeTuples, since a composite type
presumably shouldn't have system columns associated. I'd counsel
looking at all references to RELKIND_VIEW to see which places also need
to check for RELKIND_COMPOSITE_TYPE.

Yeah, after I fired off the post it occurred to me that I had neglected
to do that. I was just going through that exercise now.

Thanks for the (quick!) review. Round two will be probably sometime
tomorrow.

Joe

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:

Also, I'm not following the point of the separation between
DefineCompositeType and DefineCompositeTypeRelation; nor do I see a need
for a CommandCounterIncrement call in there.

Well the next thing I was going to work on after this was an implicitly
created composite type when creating a function. I thought maybe the
CommandCounterIncrement would be needed so that the type could be
created and then immediately used by the function.

Hm. Maybe --- it would depend on whether the function-creating code
actually tried to look at the type definition, as opposed to just using
its OID. (You'll probably want DefineCompositeType to return the type
OID, btw.) In any case, I'd be inclined to put the CCI call in the
caller not the callee, so it's only done when actually needed. It's
surely not needed for a standalone CREATE TYPE command.

regards, tom lane

Tom Lane wrote:

You have missed a number of places where this new relkind ought to
be special-cased the same way RELKIND_VIEW is --- for example
CheckAttributeNames and AddNewAttributeTuples, since a composite type
presumably shouldn't have system columns associated. I'd counsel
looking at all references to RELKIND_VIEW to see which places also need
to check for RELKIND_COMPOSITE_TYPE.

One of the places I missed was pg_dump.c. In working on pg_dump support,
I ran across a problem:

test=# CREATE TYPE "MyInt42" (internallength = 4,input = int4in,output =
int4out,alignment = int4,default = 42,passedbyvalue);
CREATE TYPE
test=# CREATE TYPE "compfoo" AS (f1 "MyInt42", f2 integer);
CREATE TYPE
test=# drop type compfoo;
DROP TYPE
test=# CREATE TYPE "compfoo" AS (f1 "MyInt42", f2 "integer");
ERROR: Type "integer" does not exist
test=# create table tbl_0 (f1 "integer");
ERROR: Type "integer" does not exist
test=# create table tbl_0 (f1 "MyInt42");
CREATE TABLE
test=# drop table tbl_0 ;
DROP TABLE
test=# create table tbl_0 (f1 integer);
CREATE TABLE

Shouldn't "integer" be recognized as a valid type?

Thanks,

Joe

Tom Lane wrote:

If you did it that way then you'd not need that ugly kluge in
RemoveType. What you'd need instead is some smarts (a kluge!?) in
setting up the dependency. Currently that dependency is made in
TypeCreate which doesn't know what sort of relation it's creating
a type for. Probably the best answer is to pull that particular
dependency out of TypeCreate, and make it (in the proper direction)
in AddNewRelationType.

Fixed.

Also, I'm not following the point of the separation between
DefineCompositeType and DefineCompositeTypeRelation; nor do I see a need
for a CommandCounterIncrement call in there.

Fixed.

You have missed a number of places where this new relkind ought to
be special-cased the same way RELKIND_VIEW is --- for example
CheckAttributeNames and AddNewAttributeTuples, since a composite type
presumably shouldn't have system columns associated. I'd counsel
looking at all references to RELKIND_VIEW to see which places also need
to check for RELKIND_COMPOSITE_TYPE.

Yup, I had missed lots of things, not the least of which was pg_dump.
New patch attached includes pg_dump, psql (\dT), docs, and regression
support.

There is also a small adjustment to the expected output file for
select-having. I was getting a regression failure based on ordering of
the results, so I added ORDER BY clauses.

Passes all regression tests. If no more objections, please apply.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:

Shouldn't "integer" be recognized as a valid type?

Not when double-quoted; you are being overzealous about quoting.

regards, tom lane

Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:

Shouldn't "integer" be recognized as a valid type?

Not when double-quoted; you are being overzealous about quoting.

I figured that out (new patch works "correctly"), but it does seem
inconsistent:

test=# create table tbl_0 (f1 "integer");
ERROR: Type "integer" does not exist
test=# create table "tbl_0" ("f1" integer);
CREATE TABLE
test=# select * from tbl_0 ;
f1
----
(0 rows)

test=# select f1 from tbl_0 ;
f1
----
(0 rows)

test=# select "f1" from tbl_0 ;
f1
----
(0 rows)

For table and column identifiers, if they were defined in all lowercase
I can either quote them, or not -- it works either way. Same for *user*
defined data types:

test=# CREATE TYPE myint42 ( internallength = 4, input = int4in, output
= int4out, default = '42', alignment = int4, storage = plain,
passedbyvalue);
CREATE TYPE
test=# create table "tbl_1" ("f1" myint42);
CREATE TABLE
test=# create table "tbl_2" ("f1" "myint42");
CREATE TABLE
test=# \d tbl_2
Table "tbl_2"
Column | Type | Modifiers
--------+---------+-----------
f1 | myint42 |

But *internal* data types only work unquoted.

Joe

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:
Shouldn't "integer" be recognized as a valid type?

Not when double-quoted; you are being overzealous about quoting.

I figured that out (new patch works "correctly"), but it does seem
inconsistent:

Would you expect "point[]" and point[] to be the same?

The actual type name is int4. int4 and "int4" act the same. Aliases
like int and integer are special keywords and so are not recognized
when quoted.

regards, tom lane

Joe Conway wrote:

There is also a small adjustment to the expected output file for
select-having. I was getting a regression failure based on ordering of
the results, so I added ORDER BY clauses.

I now see that the select-having fails differently on my PC at home from
the one at work. At home I see in postgresql.conf:

LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

and at work:

LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'

I have been running `make installcheck` instead of `make check`. I
gather that `make installcheck` does not set LOCALE to 'C' (as make
check does -- I think). Should it?

Please rip the select-having pieces out of this patch when it is
applied, or let me know and I'll submit another patch.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:

Joe Conway wrote:

There is also a small adjustment to the expected output file for
select-having. I was getting a regression failure based on ordering of
the results, so I added ORDER BY clauses.

I now see that the select-having fails differently on my PC at home from
the one at work. At home I see in postgresql.conf:

LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'

and at work:

LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'

I have been running `make installcheck` instead of `make check`. I
gather that `make installcheck` does not set LOCALE to 'C' (as make
check does -- I think). Should it?

The problem is that LC_COLLATE is (presumably) also en_US at work;
"make installcheck" hasn't got any way of changing the collation of
the preinstalled server, because that was frozen at initdb. See
the discussion in the regression-test documentation.

regards, tom lane

Joe Conway writes:

3. Modify CREATE FUNCTION to allow the implicit creation of a dependent
composite type, e.g.:

Forgive this blunt question, but: Why?

Of course I can see the answer, it's convenient, but wouldn't the system
be more consistent overall if all functions and types are declared
explicitly?

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Joe Conway writes:

3. Modify CREATE FUNCTION to allow the implicit creation of a dependent
composite type, e.g.:

Forgive this blunt question, but: Why?

Now's a *great* time for a blunt question because I haven't started
actively working on this yet. Much better than after I'm done. ;-)

Of course I can see the answer, it's convenient, but wouldn't the system
be more consistent overall if all functions and types are declared
explicitly?

And of couse you are correct. It is almost purely convenience. My
reasoning was this: if I am creating a function which returns a
composite type, then the fact that a named composite type exists is
superfluous to me. It would be more natural for me to do:

CREATE FUNCTION foo() RETURNS SETOF (f1 int, f2 text);

than to do:

CREATE TYPE some_arbitrary_name AS (f1 int, f2 text);
CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name;

But I admit it is only a "nice-to-have", not a "need-to-have".

How do others feel? Do we want to be able to implicitly create a
composite type during function creation? Or is it unneeded bloat?

I prefer the former, but don't have a strong argument against the latter.

Joe

I think it buys the same as SERIAL does for sequences.

Is it likely to have more than one function using a complex type like
that? If not, then allowing it's creation (not enforcing) could be
useful.

Rod Taylor wrote:

I think it buys the same as SERIAL does for sequences.

That's a great analogy.

Is it likely to have more than one function using a complex type like
that? If not, then allowing it's creation (not enforcing) could be
useful.

That's what I was thinking. In cases where you want to use the type for
several functions, use CREATE TYPE. If you only need the type for one
function, let the function creation process manage it for you.

Joe

-----Original Message-----
From: Joe Conway [mailto:mail@joeconway.com]
Sent: Friday, August 09, 2002 4:04 PM
To: Peter Eisentraut
Cc: pgsql-hackers
Subject: Re: [HACKERS] Proposal: stand-alone composite types

Peter Eisentraut wrote:

Joe Conway writes:

3. Modify CREATE FUNCTION to allow the implicit creation of

a dependent

composite type, e.g.:

Forgive this blunt question, but: Why?

Now's a *great* time for a blunt question because I haven't started
actively working on this yet. Much better than after I'm done. ;-)

Of course I can see the answer, it's convenient, but wouldn't the
system be more consistent overall if all functions and types are
declared explicitly?

And of couse you are correct. It is almost purely convenience. My
reasoning was this: if I am creating a function which returns a
composite type, then the fact that a named composite type exists is
superfluous to me. It would be more natural for me to do:

CREATE FUNCTION foo() RETURNS SETOF (f1 int, f2 text);

than to do:

CREATE TYPE some_arbitrary_name AS (f1 int, f2 text);
CREATE FUNCTION foo() RETURNS SETOF some_arbitrary_name;

Actually, the second looks a lot more natural to me. As in most
programming languages, you define the type/class first, and then you can
use the type/class as an object.

Further, I don't want to have to remember the implementation details,
unless I need to dig into them.
So:

CREATE TYPE locator AS (First_Name varchar, Last_Name varchar, City
varchar, State_Province char(2), Country varchar);

CREATE FUNCTION CustomerList(varchar specification) RETURNS SETOF
locator;

Seems far more natural and convenient to me.