Thread: Specifying Rowtypes
So currently the only way to specify a row type is by using a table, right? E.g.: CREATE TABLE t2_retval (value1 int NOT NULL DEFAULT -1,value2 int NOT NULL,value3 int ); Are there plans to add another way of declaring this sort of thing so that I don't have to add a new table to my schema for every function that returns a rowtype? Second, it seems that there's still a problem with NULLs here: CREATE FUNCTION t2()RETURNS t2_retval AS ' DECLAREretval t2_retval%ROWTYPE; BEGINSELECT INTO retval null, null,null;RETURN retval; END ' LANGUAGE 'plpgsql'; This is returning a row that (to my mind) doesn't match the type of the table above, because it's returning null for non-nullable columns: cjs=> select coalesce(value1, -999), coalesce(value2, -999), cjs-> coalesce(value3, -999) from t2();case | case | case ------+------+-------999 | -999 | -999 (1 row) (You get the same result if you delete the SELECT INTO line above.) Am I misunderstanding something here, or is this a bug? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Wed, 29 Jan 2003, Curt Sampson wrote: > > So currently the only way to specify a row type is by using a table, > right? E.g.: > > CREATE TABLE t2_retval ( > value1 int NOT NULL DEFAULT -1, > value2 int NOT NULL, > value3 int > ); > > Are there plans to add another way of declaring this sort of thing so > that I don't have to add a new table to my schema for every function > that returns a rowtype? You can also return records at which point you have to give a definition at select time. create function aa1() returns record as 'select 1,2;' language 'sql'; select * from aa1() as aa1(a int, b int); Also, for defined types like that, you probably want to use CREATE TYPE ... AS rather than CREATE TABLE. > Second, it seems that there's still a problem with NULLs here: > > CREATE FUNCTION t2() > RETURNS t2_retval > AS ' > DECLARE > retval t2_retval%ROWTYPE; > BEGIN > SELECT INTO retval null, null, null; > RETURN retval; > END > ' LANGUAGE 'plpgsql'; > > This is returning a row that (to my mind) doesn't match the type of the > table above, because it's returning null for non-nullable columns: I believe only the column names and types are considered for purposes of this. Check constraints and the like defined on the column aren't applied either. I can see arguments for both ways since things like foreign keys or the not yet supported check constraints with subselects would seem to have not terribly meaningful results. Although if you make the column on a domain and the domain has a constraint it does seem to be applied.
No, in 7.3 you can create anonymous composite types using the CREATE TYPE command. Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Curt Sampson > Sent: Wednesday, 29 January 2003 1:45 PM > To: PostgreSQL Development > Subject: [HACKERS] Specifying Rowtypes > > > > So currently the only way to specify a row type is by using a table, > right? E.g.: > > CREATE TABLE t2_retval ( > value1 int NOT NULL DEFAULT -1, > value2 int NOT NULL, > value3 int > ); > > Are there plans to add another way of declaring this sort of thing so > that I don't have to add a new table to my schema for every function > that returns a rowtype? > > Second, it seems that there's still a problem with NULLs here: > > CREATE FUNCTION t2() > RETURNS t2_retval > AS ' > DECLARE > retval t2_retval%ROWTYPE; > BEGIN > SELECT INTO retval null, null, null; > RETURN retval; > END > ' LANGUAGE 'plpgsql'; > > This is returning a row that (to my mind) doesn't match the type of the > table above, because it's returning null for non-nullable columns: > > cjs=> select coalesce(value1, -999), coalesce(value2, -999), > cjs-> coalesce(value3, -999) from t2(); > case | case | case > ------+------+------ > -999 | -999 | -999 > (1 row) > > (You get the same result if you delete the SELECT INTO line above.) > > Am I misunderstanding something here, or is this a bug? > > cjs > -- > Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org > Don't you know, in this new Dark Age, we're all light. --XTC > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Curt Sampson <cjs@cynic.net> writes:
> So currently the only way to specify a row type is by using a table,
No, as of 7.3 there's CREATE TYPE foo AS (column list). But ...
> This is returning a row that (to my mind) doesn't match the type of the
> table above, because it's returning null for non-nullable columns:
The current behavior of a rowtype doesn't include any constraint checks.
Feel free to design a solution ...
regards, tom lane
On Tue, 28 Jan 2003, Stephan Szabo wrote: > You can also return records at which point you have to give a definition > at select time. > > create function aa1() returns record as 'select 1,2;' language 'sql'; > select * from aa1() as aa1(a int, b int); Yeah, I tried that approach too, but it got ugly quickly. Changing that line in all my unit tests every time I changed the signature of the return value was a fair amount of extra effort. > Also, for defined types like that, you probably want to use > CREATE TYPE ... AS rather than CREATE TABLE. That's much better! Thanks! > I believe only the column names and types are considered for purposes of > this. Check constraints and the like defined on the column aren't applied > either. I can see arguments for both ways since things like foreign keys > or the not yet supported check constraints with subselects would seem to > have not terribly meaningful results. Well, it might make sense to declare that you can't return anything that couldn't, in the current transaction, be inserted into that table. But easier, perhaps, would just be to provide the ability to add limited constraints to CREATE TYPE, and only honour the constranints that can be applied in a CREATE TYPE statement. > Although if you make the column on a domain and the domain has a > constraint it does seem to be applied. Hmmm. Interesting. This would be basically what I described above, then, wouldn't it, except it doesn't work for me (with types or tables): CREATE DOMAIN nonnull_int ASintDEFAULT 0CONSTRAINT nonnull_int_not_null NOT NULL; CREATE TYPE t2_retval AS (value1 nonnull_int,value2 nonnull_int,value3 nonnull_int ); CREATE FUNCTION t2()RETURNS SETOF t2_retval AS ' DECLAREretval t2_retval%ROWTYPE; BEGINSELECT INTO retval 1;RETURNNEXT retval;SELECT INTO retval 1, 2, 3;RETURN NEXT retval;SELECT INTO retval null, null, null;RETURN NEXT retval;RETURN; END ' LANGUAGE 'plpgsql'; SELECT * FROM t2(); ...produces rows with nulls in them. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Wed, 29 Jan 2003, Curt Sampson wrote: > On Tue, 28 Jan 2003, Stephan Szabo wrote: > > I believe only the column names and types are considered for purposes of > > this. Check constraints and the like defined on the column aren't applied > > either. I can see arguments for both ways since things like foreign keys > > or the not yet supported check constraints with subselects would seem to > > have not terribly meaningful results. > > Well, it might make sense to declare that you can't return anything that > couldn't, in the current transaction, be inserted into that table. > > But easier, perhaps, would just be to provide the ability to add limited > constraints to CREATE TYPE, and only honour the constranints that can be > applied in a CREATE TYPE statement. That's probably alot more reasonable. The problem with saying anything that couldn't be returned in the current transaction is that the state of that by the time the function is done could be different than when the row is constructed in the foreign key case (and check with subselect). Then there's also a question of before triggers because those are sometimes used to force rows into a particular mold (set this field to this if it doesn't meet some criteria). Hmm, also we might have problems with unique and primary key right now as well. > > Although if you make the column on a domain and the domain has a > > constraint it does seem to be applied. > > Hmmm. Interesting. This would be basically what I described above, then, > wouldn't it, except it doesn't work for me (with types or tables): > > CREATE DOMAIN nonnull_int AS > int > DEFAULT 0 > CONSTRAINT nonnull_int_not_null NOT NULL; > > CREATE TYPE t2_retval AS ( > value1 nonnull_int, > value2 nonnull_int, > value3 nonnull_int > ); > > CREATE FUNCTION t2() > RETURNS SETOF t2_retval > AS ' > DECLARE > retval t2_retval%ROWTYPE; > BEGIN > SELECT INTO retval 1; > RETURN NEXT retval; > SELECT INTO retval 1, 2, 3; > RETURN NEXT retval; > SELECT INTO retval null, null, null; > RETURN NEXT retval; > RETURN; > END > ' LANGUAGE 'plpgsql'; > > SELECT * FROM t2(); > > ...produces rows with nulls in them. That's a bug in pl/pgsql I believe. An sql language function (which is what I tried) requires you to cast the value into the domain which fails due to the constraint. Presumably the same requirement should hold here.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Wed, 29 Jan 2003, Curt Sampson wrote:
>> ...produces rows with nulls in them.
> That's a bug in pl/pgsql I believe.
Or a bug in the domain-constraints implementation. plpgsql just
executes the input function for the datatype --- which is the same as
the input function for the underlying type. There should probably be
some mechanism to make the input function for a domain type check the
domain's constraints.
[ thinks about it... ] We added code to COPY to check domain
constraints on top of calling the type's input function, but I wonder
whether that wasn't the wrong way to go. We'll have to hack everyplace
that calls an arbitrary input function, if we insist on that approach.
regards, tom lane