Cultural Sublimation wrote:
[has a problem because a SETOF RECORD function can return NULLs in
record attributes]
> 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.
The thing behind the RETURNS in a function is always a data type,
regardless if it is one that has been explicitly declared with
CREATE TYPE or implicitly by CREATE TABLE.
There are no NOT NULL conditions for data types.
NOT NULL only exists for table columns.
So if your function returns "movies", this is the data type
"movies" and not the table "movies". The data type does not have
constraints.
If you check attnotnull of pg_attribute, that is a column of
the table "movies", not the data type.
Hence your confusion, which is quite understandable, because
type and table have the same name.
> So, barring functions and views, is there any other way to encapsulate
> the inner workings of a query away from clients?
Have you considered an ON SELECT ... DO INSTEAD rule?
http://www.postgresql.org/docs/current/static/rules.html
You could create a table that represents the query and
define a SELECT rule on it.
Yours,
Laurenz Albe
