Re: Specifying Rowtypes

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.