Hi Christopher

Thanks for reminding me.  Forgot about the fact that you can't add to an
already subscribing set and that you have to create a temp set and
merge.  Let me try your suggestions and see where it leads.

Do you have any good examples of using PL/PGSQL for things like adding
tables?

Mark


-----Original Message-----
From: Christopher Browne [mailto:cbbrowne at ca.afilias.info] 
Sent: Wednesday, June 14, 2006 15:23
To: Mark Adan
Cc: Andrew Sullivan; slony1-general at gborg.postgresql.org
Subject: Re: [Slony1-general] Good examples of calling slony
storedprocedures

"Mark Adan" <markadan at ati.com> writes:
> Because in our system, calling the slony PL/PGSQL stored procedures
> would be easier.  What I'm trying to do is to add a new table at the
> same time our system automatically creates one.  What our system will
do
> is put the new table name into a temp table.  That temp table contains
a
> trigger to then call a function to add the table to slony.
>
>
> Below is what I am trying to do.  My apologies in advance as I am new
to
> this.  Any help would be appreciated.
>
> Mark
>
> ---------------------------------
>
>
> This is what I get from calling the addtable function:
>
> ERROR:  function _slony_cluster.setaddtable(integer, integer, name,
> integer, text) does not exist
> HINT:  No function matches the given name and argument types. You may
> need to add explicit type casts.
> CONTEXT:  PL/pgSQL function "slony_ddl" line 10 at SQL statement

Minor point:  The function signature is
   setAddTable(int4, int4, text, name, text).

Ignoring that, this is fairly sure to turn out badly.

You cannot add tables to replication sets that are active (e.g. - a
node is subscribing to them).

You need to add the tables to a "not-yet-subscribed" replication set,
so your procedure would be something like:

  _slony_cluster.storeset(42, 'new set #42');
  _slony_cluster.setaddtable(NewSlonyTableID, 42, CAST (NEW.tablename AS
name), '_my_index_name', CAST (New.tablename AS text));
  _slony_cluster.subscribeset(42, 22, 33, 't');

I'm assuming that 42 is determined as a new set ID, that the provider
is node 22, that the singular subscriber is 33, and that the PK index
is _my_index_name.

- If you have another table, you'll have to generate set #43.

- You'd more than likely want to merge those sets in with an existing
  one, eventually.

- If you have multiple subscribers, something has to handle that.

If there are just 2 nodes, you aren't worried about merging sets, and
there are good PK candidates that you can use, then the above might
just possibly work.
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)