8.41. ddlcapture(p_nodes text, p_statement text)

Function Properties

Language: PLPGSQL

Return Type: bigint

Capture an SQL statement (usually DDL) that is to be literally replayed on subscribers

declare
	c_local_node	integer;
	c_found_origin	boolean;
	c_node			text;
	c_cmdargs		text[];
	c_nodeargs      text;
	c_delim         text;
begin
	c_local_node := getLocalNodeId('_schemadoc');

	c_cmdargs = array_append('{}'::text[], p_statement);
	c_nodeargs = '';
	if p_nodes is not null then
		c_found_origin := 'f';
		-- p_nodes list needs to consist of a list of nodes that exist
		-- and that include the current node ID
		for c_node in select trim(node) from
				pg_catalog.regexp_split_to_table(p_nodes, ',') as node loop
			if not exists 
					(select 1 from sl_node 
					where no_id = (c_node::integer)) then
				raise exception 'ddlcapture(%,%) - node % does not exist!', 
					p_statement, p_nodes, c_node;
		   end if;

		   if c_local_node = (c_node::integer) then
		   	  c_found_origin := 't';
		   end if;
		   if length(c_nodeargs)>0 then
		   	  c_nodeargs = c_nodeargs ||','|| c_node;
		   else
				c_nodeargs=c_node;
			end if;
	   end loop;

		if not c_found_origin then
			raise exception 
				'ddlcapture(%,%) - origin node % not included in ONLY ON list!',
				p_statement, p_nodes, c_local_node;
       end if;
    end if;
	c_cmdargs = array_append(c_cmdargs,c_nodeargs);
	c_delim=',';
	c_cmdargs = array_append(c_cmdargs, 

           (select string_agg( seq_id::text || c_delim
		   || c_local_node ||
		    c_delim || seq_last_value)
		    FROM (
		       select seq_id,
           	   seq_last_value from sl_seqlastvalue
           	   where seq_origin = c_local_node) as FOO
			where NOT seqtrack(seq_id,seq_last_value) is NULL));
	insert into sl_log_script
			(log_origin, log_txid, log_actionseq, log_cmdtype, log_cmdargs)
		values 
			(c_local_node, pg_catalog.txid_current(), 
			nextval('sl_action_seq'), 'S', c_cmdargs);
	execute p_statement;
	return currval('sl_action_seq');
end;