Re: Cannot declare record members NOT NULL - Mailing list pgsql-general

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