Re: Cannot declare record members NOT NULL

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