Darcy Buskermolen <darcy at wavefire.com> writes:
> On November 18, 2004 07:48 am, Christopher Browne wrote:
>
>>I have the following as somewhat "version-unaware" upgrade scheme
>>which can cope with the case of needing to add a column to a table:
>>
>> create or replace function @NAMESPACE at .upgrade_sl_node () returns bool
>> as '
>> DECLARE
>> ? ? v_row ?record;
>> BEGIN
>> ? ?if add_missing_table_field(@NAMESPACE@, ''sl_node'', ''no_spool'',
>> ''boolean'') then alter table @NAMESPACE at .sl_node
>> ? ? ? ? ? ? alter column no_spool set default = ''f'';
>> ? ? ? ? update @NAMESPACE at .sl_node set no_spool = ''f'';
>> ? ? ? ? return ''t'';
>> ? ?end if;
>> ? ?return ''t'';
>> END;' language plpgsql;
>>
>> comment on function @NAMESPACE at .upgrade_sl_node() is
>> ? 'Schema changes required to upgrade to version 1.1';
>>
>> create or replace function @NAMESPACE at .add_missing_table_field (text, text,
>> text, text) returns bool as '
>> DECLARE
>> ? p_namespace alias for $1;
>> ? p_table ? ? alias for $2;
>> ? p_field ? ? alias for $3;
>> ? p_type ? ? ?alias for $4;
>> ? v_row ? ? ? record;
>> ? v_query ? ? text;
>> BEGIN
>> ? select 1 into v_row from pg_namespace n, pg_class c, pg_attribute a
>> ? ?where n.nspname = p_namespace and
>> ? ? ? ? ?c.relname = n.oid and
>> ? ? ? ? ?c.relname = p_table and
>> ? ? ? ? ?a.attrelid = c.oid and
>> ? ? ? ? ?a.attname = p_field;
>> ? if not found then
>> ? ? raise notice ''Upgrade table %.% - add field %'', p_namespace, p_table,
>> p_field; v_query := ''alter table "'' || p_namespace || ''".'' || p_table
>> || ' add column ''; v_query := v_query || p_field || '' type '' || p_type
>> || '';''; execute v_query;
>> ? ? return ''t'';
>> ? else
>> ? ? return ''f'';
>> ? end if;
>> END;' language plpgsql;
>
> So far this looks good to me, looking at how you implemented
> upgrade_sl_node in -HEAD, i see you aren't using this, nor can I
> find anyware in slonik or the like where upgrade_sl_node is called
> when an UPGRADE FUNCTIONS gets issued. I missing something stupid
> here ?

It's something of a "stub" function, at this point; the notion is to
have a function there for upgrade activities to accrete into.

Further, it's a bit of a "trial balloon;" if it seems sensible, then
it's sensible to continue.  If it's not, then it should disappear...
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)