Re: Specifying Rowtypes

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 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.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC