Re: Specifying Rowtypes

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.