From: | Cultural Sublimation <cultural_sublimation(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Cannot declare record members NOT NULL |
Date: | 2007-09-12 19:32:13 |
Message-ID: | 540511.75258.qm@web63405.mail.re1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo De León | 2007-09-12 19:50:39 | Re: Cannot declare record members NOT NULL |
Previous Message | sharmi Joe | 2007-09-12 19:07:58 | pgpool and foreign key on partitioned tables |