For those who follow the NORM project and specifically our most recent addition, NORM_GEN, I have some exciting news. As we continue the work on automation of the creation of the functions and on making them more usable for application developers, we made two critical improvements:
First, the build_conditions function now accepts the MongoDB syntax.
For example, the call:
select norm_gen.build_conditions(
'{
"User account":{
"phone_type":"cell",
"email_priority":"primary",
"account":{"last_name":"johns",
"emails":{"email_address":{"$like":"%gmail%"}},
"dob":{"$gt":"1901-01-01"},
"phones":{"phone_number":{"$like":"312%"}}
}
}}'
::json);
will generate the following conditions:
account_id IN (
select account_id from norm.phone where
phone_id IN (
select phone_type_id from norm.phone_type where
phone_type = ('cell'::text) )
AND phone LIKE ('312%'::text) )
AND account_id IN (
select account_id from norm.email where
email_id IN (
select email_priority_id from norm.email_priority where
email_priority = ('primary'::text) )
AND email LIKE ('%gmail%'::text) )
AND last_name = ('johns'::text) AND dob > ('1901-01-01'::date)
Second, for any specific hierarchy, we can generate its own search function using norm_gen.generate_search_generic_function
.
For example, generic search for account will look like this:
create or replace function norm.account_search_generic(p_search_json json
) returns norm.account_record[]
language 'plpgsql'
as $BODY$
declare
v_result norm.account_record[];
v_sql text;
begin
v_sql:=norm_gen.nested_root('User account')||
$$ where $$||norm_gen.build_conditions(('{
"User account":'||p_search_json::text||'}')
::json);
execute v_sql into v_result;
return (v_result);
end;
$BODY$;
Note that calling norm_gen.nested_root allows us to pick up a new type definition instantaneously after it was changed.
You do not need to write this search; everything is done automatically. Now you can call a function
select norm.account_search_generic($${
"phone_type":"cell",
"email_priority":"primary",
"account":{"last_name":"johns",
"emails":{"email_address":{"$like":"%gmail%"}},
"dob":{"$gt":"1901-01-01"},
"phones":{"phone_number":{"$like":"312%"}}
}
}$$::json)
And it will return exactly the result you expect!
{"account_id":1,
"username":"aliceacct1",
"last_name":"johns",
"first_name":"alice",
"dob":"1996-04-01",
"emails": [{"email":"alicejons@gmail.com","email_id":1,"email_priority":"primary","email_priority_id":1},{"email":"johnsonbs@hotmail.com","email_id":2,"email_priority":"primary","email_priority_id":1}],
"phones":[{"phone_id":1,"phone_type":"cell","phone":"2021234567","phone_type_id":2},{"phone_id":2,"phone_type":"cell","phone":"3121233344","phone_type_id":2}]}
And no application developer needs to know that there is pure PostgreSQL inside!
Dear application developers, software engineers, and anybody else who might be interested – would you try it out :)?