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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
