Dear list,
Here's the situation: I want to create a functional API to a Postgresql
database, in such a way that instead of issuing raw SQL commands (SELECT,
INSERT, etc), the client will only invoke functions from this API.
For example, to get all people in the database, the client will invoke
function "SELECT * FROM get_people()" instead of manually doing a SELECT
over the 'people' table:
create table people (id serial primary key, name text not null, age int4 not null);
create type result as (name text, age int4);
create function get_people() returns setof result as $$ select name, age from people $$ language sql;
But here's the problem: I want the client side library to check the
attnotnull field from pg_attribute to determine if it may be null.
However, in a user defined type like 'result' above, all the fields
are marked as possibly being null.
I have tried the following, but it's not accepted:
create type result as (name text not null, age int4 not null);
I also tried manually setting the attnotnull value in result to TRUE,
but it doesn't work as expected.
I also thought of creating a dummy table for the return results, but
I'm afraid the performance penalty might be too great. Could you give
me some help in solving this problem?
Thank you!
Jean