Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions.

the system uses one scheme at a time, as if they were separate databases. So when i install an update i need to update the 15 schemes in the database.

My problem comes when we update the database with changes in functions and triggers, because I need to modify in each function what scheme uses for each scheme; example:

CREATE OR REPLACE FUNCTION cliente11."restablecerComprobante"("numeroComprobante" text)
  RETURNS integer AS
$BODY$
DECLARE idComprobante INTEGER;
DECLARE tipoComprobante TEXT;
DECLARE idCliente INTEGER;
DECLARE fechaComprobante DATE;
DECLARE totalComprobante NUMERIC;
DECLARE estadoComprobante INTEGER;
DECLARE percepcionIIBB NUMERIC;
DECLARE percepcionIIBBxKg NUMERIC;
DECLARE percepcionIVA NUMERIC;
BEGIN

    -- cargo los datos del comprobante
    SELECT id, tipo, cli_id, fecha_comprobante, total_comprobante, percepcion_iibb, percepcion_iibb_kg, percepcion_iva INTO idComprobante, tipoComprobante, idCliente, fechaComprobante, totalComprobante, percepcionIIBB, percepcionIIBBxKg, percepcionIVA FROM cliente11.lista_comprobantes_a_liberar WHERE estado = 0 AND numero_comprobante ILIKE "numeroComprobante";

    -- otras consultas...

    -- genero las comisiones
    SELECT cliente11."generarComisionesVenta"(idComprobante, tipoComprobante) INTO estadoComprobante;

RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Is there a more practical way to update the 15 schemes without having to modify the script to run in each scheme?

share|improve this question
    
When you write scheme are you referring to schema (I guess the plural would be schemas or schemata)? If so, then not in simple terms. I suggest that you write a script to do the updates for you to multiple schemas since you are already using PL/pgSQL. –  efesar Aug 12 '13 at 15:07
add comment

1 Answer

up vote 2 down vote accepted

Why don't you set the variable search_path to be each schema in turn?

See http://www.postgresql.org/docs/current/static/runtime-config-client.html

share|improve this answer
    
it work on the same database, but when i use dblink to access another db with schemas fail. i need to set it too on that conection? how i do that? –  aegis Aug 12 '13 at 18:05
    
Connect to the database directly. Database links should be used for querying, possibly DML, but not DDL, IMHO. I'm not even sure if DDL is possible via dblinks. In Oracle it certainly isn't. –  Colin 't Hart Aug 12 '13 at 20:28
    
i use dblink to call a function in the other DB, using dblink_perform, that DB also use schemas. what can i do? –  aegis Aug 12 '13 at 23:26
    
Connect to the database directly and perform the DDL there. –  Colin 't Hart Aug 13 '13 at 10:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.