Thread: Specifying Rowtypes

Specifying Rowtypes

From
Curt Sampson
Date:
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
 


Re: Specifying Rowtypes

From
Stephan Szabo
Date:
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.



Re: Specifying Rowtypes

From
"Christopher Kings-Lynne"
Date:
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
>



Re: Specifying Rowtypes

From
Tom Lane
Date:
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


Re: Specifying Rowtypes

From
Curt Sampson
Date:
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
 


Re: Specifying Rowtypes

From
Stephan Szabo
Date:
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.



Re: Specifying Rowtypes

From
Tom Lane
Date:
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