Why do you create an extra type for that? Just have your method return "movies" i.e. CREATE FUNCTION get_movies () RETURNS SETOF movies ... ... HTH Uwe On Wednesday 12 September 2007, Cultural Sublimation wrote: > Hi, > > I am not sure if this qualifies as a bug report or a feature request, > but I don't see any way to tell Postgresql that the members of a record > cannot be NULL. This causes all kinds of problems when this record > is used to declare the return type of a function. Suppose I had the > following table: (note that all columns are NOT NULL) > > CREATE TABLE movies > ( > movie_id int4 UNIQUE NOT NULL, > movie_name text NOT NULL, > PRIMARY KEY (movie_id) > ); > > > Suppose also that I didn't want the clients to query the table directly, > but instead they have to go through a function "get_movies" which returned > a record of type "get_movies_t": > > CREATE TYPE get_movies_t AS > ( > movie_id int4, > movie_name text > ); > > > CREATE FUNCTION get_movies () > RETURNS SETOF get_movies_t > LANGUAGE sql STABLE > AS > $$ > SELECT movie_id, movie_name FROM movies; > $$; > > > The problem is that Postgresql tells the client that the function returns > two columns, both of which can be NULL, and this makes a mess on the > client side. Is there anyway I can tell Postgresql that the columns of > get_movies_t are NOT NULL? > > If this is (yet another) defect in the SQL standard, can someone suggest > an alternative that would get around it? > > Thanks for the help! > C.S. > > > > > ___________________________________________________________________________ >_________ Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get > listings, and more! http://tv.yahoo.com/collections/3658 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
