0) Connect to a postgresql database
1) Run SQL script with a user with permissions to make schema "utils" and functions inside that schema.
2) If the script run without problems, you can make the stored procedures of a table executing:
select utils.generatestoredprocedures('tableschema', 'tablename', 'schemaTarget');
3) Its make 5 stored procedures in the schema 'schemaTarget' (obeying the CRUD structure), there prefix names are:
get: select by any field of the table, if you dont want filter by a field, just send null
keyword. If all fields of the primary key arent null
, the filter is by all fields of the primary key allowing the index use..
upd: update all the fields filtering through all fields of primary key.
del: delete a row filtering through all fields of primary key.
ins: Insert a row using all fields given.
save: Verify if the row exists by the primary key fields and if exists that row, update it, otherwise, insert it.
4) You can generate all the CRUD functions (5 functions per table), executing the following SQL:
select utils.generatestoredprocedures('tableschema', '', 'schemaTarget');
Just typing '' on table name, its generate all CRUD functions of all tables of schema source.
Enjoy this!!