Re: [HACKERS] Specifying Rowtypes
No, in 7.3 you can create anonymous composite types using the CREATE TYPE command. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 [EMAIL PROTECTED] +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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Specifying Rowtypes
Curt Sampson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 [EMAIL PROTECTED] +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 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Specifying Rowtypes
Stephan Szabo [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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 [EMAIL PROTECTED] +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
