create_tables.sql (3519B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 DROP PROCEDURE IF EXISTS merchant.sync_instance_procedures(BIGINT); 18 CREATE PROCEDURE merchant.sync_instance_procedures( 19 in_merchant_serial BIGINT 20 ) 21 LANGUAGE plpgsql 22 AS $$ 23 DECLARE 24 rec RECORD; 25 r RECORD; 26 my_schema TEXT; 27 my_schema_name TEXT; 28 v_new_def TEXT; 29 BEGIN 30 my_schema = format('merchant_instance_%s', in_merchant_serial); 31 my_schema_name = my_schema || '.'; 32 33 -- First remove the existing routines: CREATE OR REPLACE FUNCTION cannot 34 -- change the signature or the return type of an existing function, so 35 -- without dropping first, syncing a routine whose arguments changed fails 36 -- with 'cannot change return type of existing function'. 37 -- Trigger functions are deliberately excluded: the triggers of the 38 -- instance schema (created by the merchant_NNNN_init() fixups) depend on 39 -- them, and are not part of what we copy over here. Their signature is 40 -- fixed at '() RETURNS TRIGGER' anyway, so CREATE OR REPLACE always 41 -- suffices for them. 42 FOR r IN 43 SELECT p.oid::REGPROCEDURE AS signature, 44 CASE p.prokind 45 WHEN 'p' THEN 'PROCEDURE' 46 ELSE 'FUNCTION' 47 END AS kind 48 FROM pg_proc p 49 JOIN pg_namespace n 50 ON n.oid = p.pronamespace 51 WHERE n.nspname = my_schema 52 AND p.prokind IN ('f', 'p') 53 AND p.prorettype <> 'pg_catalog.trigger'::REGTYPE 54 LOOP 55 EXECUTE format ('DROP %s %s', 56 r.kind, 57 r.signature); 58 END LOOP; 59 60 FOR r IN 61 SELECT pg_get_functiondef(p.oid) AS definition 62 FROM pg_proc p 63 JOIN pg_namespace n 64 ON n.oid = p.pronamespace 65 WHERE n.nspname = 'merchant_instances' 66 LOOP 67 v_new_def := replace( 68 r.definition, 69 'merchant_instances.', 70 my_schema_name 71 ); 72 EXECUTE v_new_def; 73 END LOOP; 74 75 FOR r IN 76 SELECT pg_get_triggerdef(t.oid, true) AS trigger_def 77 FROM pg_trigger t 78 JOIN pg_class c 79 ON c.oid = t.tgrelid 80 JOIN pg_namespace n 81 ON n.oid = c.relnamespace 82 WHERE n.nspname = 'merchant_instances' 83 AND NOT t.tgisinternal 84 LOOP 85 v_new_def := replace( 86 r.trigger_def, 87 'merchant_instances.', 88 my_schema_name 89 ); 90 EXECUTE v_new_def; 91 END LOOP; 92 END $$; 93 94 COMMENT ON PROCEDURE merchant.sync_instance_procedures(BIGINT) 95 IS 'Synchronizes procedures and triggers for the given instance by copying the current version from merchant_instances into the per-instance SCHEMA'; 96 97 98 DROP PROCEDURE IF EXISTS merchant.sync_all_instance_procedures(); 99 CREATE PROCEDURE merchant.sync_all_instance_procedures() 100 LANGUAGE plpgsql 101 AS $$ 102 DECLARE 103 merchant_id BIGINT; 104 BEGIN 105 FOR merchant_id IN 106 SELECT merchant_serial 107 FROM merchant.merchant_instances 108 LOOP 109 CALL merchant.sync_instance_procedures 110 (merchant_id); 111 END LOOP; 112 END $$;