Re: Cannot declare record members NOT NULL

>
> I haven't tried it with a view yet - so this may or may not work. But try
> giving it a shot by declaring a view
>
> create view vmovies as
> select movie_id,movie_text from movies
>
> and let your function return setof vmovies
>
> Maybe that works - I think it should.

Hey,

Thanks for the help. But no, not even by declaring a view it works.
It follows pretty much the same pattern that I just described in
response to Osvaldo Kussama's message. If you do a "naked" SELECT
on the movies table, Postgresql correctly tells the client that
the types are NOT NULL. However, if you do the same via the
function get_movies, the types are transformed into NULL.
This is some very odd behaviour...

Cheers,
C.S.

P.S. The code using the view:

SELECT movie_id, movie_name FROM movies;
=> returns a SETOF of (int4 NOT NULL, text NOT NULL)

SELECT movie_id, movie_name FROM get_movies ();
=> returns a SETOF of (int4 NULL, text NULL)

CREATE TABLE movies
(
movie_id int4 UNIQUE NOT NULL,
movie_name text NOT NULL,
PRIMARY KEY (movie_id)
);

CREATE VIEW view_get_movies AS
SELECT movie_id, movie_name FROM movies;

CREATE FUNCTION get_movies ()
RETURNS SETOF view_get_movies
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies;
$$;

____________________________________________________________________________________
Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV.
http://tv.yahoo.com/