Cannot declare record members NOT NULL

On 9/12/07, Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

Thanks for the help!

Not really following you, but try these:

CREATE OR REPLACE FUNCTION GET_MOVIES ()
RETURNS SETOF MOVIES
LANGUAGE SQL STABLE
AS
$$
SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES;
$$;

-- OR --

CREATE OR REPLACE FUNCTION GET_MOVIES (OUT MOVIE_ID INT4, OUT MOVIE_NAME TEXT)
RETURNS SETOF RECORD
LANGUAGE SQL STABLE
AS
$$
SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES;
$$;

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

Why do you create an extra type for that?
Just have your method return "movies"

Hi,

Thanks for the answer. The simple example obfuscates the fact that in reality
the table has a few extra columns that are omitted from get_movies_t.
Therefore, I cannot return "movies".

However, your answer did give me an idea: instead of declaring "get_movies_t"
as a record, I declare it as dummy table, and return that (see code at the
end).
This works, though it is *very* ugly. Any other ideas?

Thanks,
C.S.

CREATE TABLE get_movies_t
(
movie_id int4 NOT NULL,
movie_name text NOT NULL
);

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

____________________________________________________________________________________
Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7

Hi again,

However, your answer did give me an idea: instead of declaring
"get_movies_t" as a record, I declare it as dummy table, and return
that (see code at the end).
This works, though it is *very* ugly. Any other ideas?

My apologies, but it turns out that this solution doesn't work after
all. Postgresql still insists that movie_id and movie_name are NULL,
though I explicitly declared them as NOT NULL:

CREATE TABLE get_movies_t
(
movie_id int4 NOT NULL,
movie_name text NOT NULL
);

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

This bug seems to obvious to have been generally missed. I have to
wonder if it is not a bug in my particular build of Postgresql -- I am
running version 8.2.4 straight from the Ubuntu repositories (package
version 8.2.4-1~edgy1).

Could someone else try the code and check if Postgresql returns
the correct type?

Thanks,
C.S.

____________________________________________________________________________________
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&amp;p=graduation+gifts&amp;cs=bz

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.

Uwe

On Wednesday 12 September 2007, Cultural Sublimation wrote:

Why do you create an extra type for that?
Just have your method return "movies"

Hi,

Thanks for the answer. The simple example obfuscates the fact that in
reality the table has a few extra columns that are omitted from
get_movies_t. Therefore, I cannot return "movies".

However, your answer did give me an idea: instead of declaring
"get_movies_t" as a record, I declare it as dummy table, and return that
(see code at the end).
This works, though it is *very* ugly. Any other ideas?

Thanks,
C.S.

CREATE TABLE get_movies_t
(
movie_id int4 NOT NULL,
movie_name text NOT NULL
);

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

___________________________________________________________________________
_________ Shape Yahoo! in your own image. Join our Network Research Panel
today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
UC

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

Cultural Sublimation escreveu:

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?

CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$

Osvaldo

CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$

Hey,

Thanks for the suggestion. Unfortunately, it still doesn't work.
Here is what Postgresql is telling the client for two different
statements, one using a direct SELECT, the other using the get_movies
function:

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)

One note: I know this information because the client is using the
"describe" feature of Postgresql to retrieve the types returned
by a statement.

So, is this a bug or what?

Cheers,
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

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/

--- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

CREATE FUNCTION get_movies ()
RETURNS SETOF get_movies_t
LANGUAGE sql STABLE
AS
$$
SELECT movie_id, movie_name FROM movies
WHERE movie_id NOT NULL AND movie_name NOT NULL;
$$

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

I don't know if this will work, but here is another idea:

SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );

Regards,
Richard Broersma Jr.

I don't know if this will work, but here is another idea:

SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );

Hi,

Nope. That's not even valid syntax...
But thanks for effort, anyway!

Cheers,
C.S.

____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&amp;sid=396545469

On Wed, Sep 12, 2007 at 02:22:46PM -0700, Cultural Sublimation wrote:

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

I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:

One note: I know this information because the client is using the
"describe" feature of Postgresql to retrieve the types returned
by a statement.

The describe clearly can't tell if the result is always going to be
NULL or not.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

--- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

I don't know if this will work, but here is another idea:

SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );

Hi,

Nope. That's not even valid syntax...

It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return types:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html

notice this "from-type" listing for functions.

Regards,
Richard Broersma Jr.

Cultural Sublimation <cultural_sublimation@yahoo.com> writes:

This bug seems to obvious to have been generally missed.

It's not a bug: there is no mechanism enforcing that the result of a
function can't be NULL.

For functions returning scalars you can get the effect by declaring the
result as being of a domain with a not null constraint, but those
things are actually quite unpleasant to use for anything other than
a shorthand for making a table constraint. Check the archives for loud
gripes from people who tried to use not-null-constrained domains in
plpgsql since plpgsql started to actually enforce that. (Some of them
seemed to have this curious idea that the constraint should only be
enforced when it was convenient for them ... and not, say, instantly
when the variable was declared. But I digress.)

In any case, it appears to me that your gripe has little to do with
whether there's actually any enforcement of the not-null condition,
and much to do with whether some unspecified client-side software
thinks the query result column is guaranteed not null. Most likely
you're going to have to fix the client-side software, because it's
extrapolating things that Postgres does not tell it.

regards, tom lane

Richard Broersma Jr wrote:

--- Cultural Sublimation <cultural_sublimation@yahoo.com> wrote:

I don't know if this will work, but here is another idea:

SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );

Hi,

Nope. That's not even valid syntax...

It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return types:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html

notice this "from-type" listing for functions.

Yeah, but it doesn't allow the NOT NULL specification.

I don't think there's any way to do what the OP wants ATM.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
Voy a acabar con todos los humanos / con los humanos yo acabar�
voy a acabar con todos / con todos los humanos acabar� (Bender)

Hi Tom,

In any case, it appears to me that your gripe has little to do with
whether there's actually any enforcement of the not-null condition,
and much to do with whether some unspecified client-side software
thinks the query result column is guaranteed not null. Most likely
you're going to have to fix the client-side software, because it's
extrapolating things that Postgres does not tell it.

That's not entirely true. The client software obtains the type
information by querying Postgresql, namely by checking the attnotnull
column in the pg_attribute catalog. Therefore, this is not an
inference error on the client side, but instead a case of Postgresql
providing wrong information.

Well, we could argue all day on whether this is a bug or a feature,
but the fact is that it is a huge setback for me. I wanted my clients
to access the database indirectly, via a function such as get_movies,
but this problem makes that impossible.

I also thought that instead of using a function, I could create a view,
and the clients could use it to access the database. But there's also
a problem with this solution: as far as I'm aware, views in Postgresql
cannot accept parameters. While the simple example does not need
parametrized views, the real world app would need them.

So, barring functions and views, is there any other way to encapsulate
the inner workings of a query away from clients?

Thanks again for your help,
C.S.

____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&amp;sid=396545469

Hi,

I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:

Yeah, that is the correct semantics: "it can be NULL". It does make
a world of difference on the client side, because an "int4 never NULL"
is a different type from "int4 possibly NULL". The type mismatch
means the program won't even compile.

Cheers,
C.S.

____________________________________________________________________________________
Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7

Cultural Sublimation wrote:

Hi,

I presume you mean that the server is saying the column can be NULL,
not that it is actually NULL, since:

Yeah, that is the correct semantics: "it can be NULL". It does make
a world of difference on the client side, because an "int4 never NULL"
is a different type from "int4 possibly NULL". The type mismatch
means the program won't even compile.

Interesting. What language are you using?

Interesting. What language are you using?

OCaml. The type-safety comes from the PG'OCaml bindings, which basically
check if the types in the database are consistent with the types in the
program. It's very neat technology, but unfortunately sometimes it seems
too advanced for its own good, since it exposes quite a few weaknesses
and inconsistencies in Postgresql...

Cheers,
C.S.

____________________________________________________________________________________
Check out the hottest 2008 models today at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html

Cultural Sublimation <cultural_sublimation@yahoo.com> writes:

Interesting. What language are you using?

OCaml. The type-safety comes from the PG'OCaml bindings, which basically
check if the types in the database are consistent with the types in the
program.

Unfortunately for you, they are not different types. If the OCaml
binding thinks they are, it's the binding's problem; especially since
the binding seems to be using a completely lame method of trying to tell
the difference.

regards, tom lane