Re: In processing DDL, when does pg_catalog get updated?

Tom ~

Good idea. The grisly details are as follows.

****************************

Here is the DDL script (generated from PowerDesigner 10.1.0.1134):

/*==============================================================*/
/* DBMS name: PostgreSQL 7.3 */
/* Created on: 12/30/2005 11:08:02 AM */
/*==============================================================*/

SET search_path TO public;

/*==============================================================*/
/* Table: e_mail_address */
/*==============================================================*/
create table e_mail_address (
pop_id INT8 not null default
nextval('pop_seq'),
effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
CURRENT_TIMESTAMP,
invisible_id BIGSERIAL not null,
e_mail_type VARCHAR(255) null,
expiration_date_and_time TIMESTAMP WITH TIME ZONE null default 'infinity',
user_name VARCHAR(255) not null,
domain_name VARCHAR(255) not null,
use_this_e_mail_for_administrative_matters BOOL null,
use_this_e_mail_for_teaching_and_practice BOOL null,
use_this_e_mail_for_personal_messages BOOL null,
omit_this_e_mail_from_school_directory BOOL null,
comments VARCHAR(4000) null,
constraint PK_e_mail_address_priority_pk primary key (pop_id,
effective_date_and_time, invisible_id),
constraint fk_contact___e_mail_address foreign key (pop_id)
references pop (pop_id)
on delete cascade on update cascade,
constraint fk_e_mail_type___e_mail_address foreign key (e_mail_type)
references e_mail_type (e_mail_type)
on delete cascade on update cascade
)
INHERITS (when_and_who)
WITH OIDS;

SELECT gen_sequences('e_mail_address', 'public');

****************************

Here is the function "gen_sequences" that is evoking the error:

CREATE OR REPLACE FUNCTION gen_sequences ( VARCHAR, VARCHAR ) RETURNS
VARCHAR AS
'
DECLARE
table_name ALIAS FOR $1;
schema_name ALIAS FOR $2;
this_table RECORD;
dummy RECORD;
sequence_name VARCHAR;
cre_seq_arr VARCHAR [] := ''{}'';
cre_seq_code VARCHAR := '''';
BEGIN
EXECUTE ''SET search_path TO '' || schema_name;

/* Create a CREATE SEQUENCE statement for the sequence
of each sequence-assigned column,
if the sequence doesnt exist already. */
FOR this_table IN
SELECT c.column_name AS sub_idcol,
c.column_default AS default_expr
FROM information_schema.columns c
WHERE c.table_name = table_name
AND c.table_schema = schema_name
AND c.column_default LIKE ''nextval%''
LOOP
sequence_name := split_part(this_table.default_expr, '''''''',
2);
IF NOT EXISTS (SELECT 1
FROM pg_catalog.pg_class AS t,
pg_catalog.pg_namespace AS s
WHERE t.relname = sequence_name
AND s.nspname = schema_name
AND t.relnamespace = s.oid
AND t.relkind = ''S'')
THEN
IF array_upper(cre_seq_arr, 1) IS NULL THEN
cre_seq_arr[1] := ''CREATE SEQUENCE '' || sequence_name
|| '';'' ;
ELSE
cre_seq_arr[array_upper(cre_seq_arr, 1) + 1] := ''CREATE
SEQUENCE '' || sequence_name || '';'' ;
END IF;
cre_seq_code := cre_seq_code ||
cre_seq_arr[array_upper(cre_seq_arr, 1)] || ''
'';
END IF;
END LOOP;

/* Execute the CREATE SEQUENCE statements, if any. */
IF array_upper(cre_seq_arr, 1) IS NOT NULL THEN
FOR n IN 1..array_upper(cre_seq_arr, 1) LOOP
EXECUTE cre_seq_arr[n];
END LOOP;
END IF;
RETURN cre_seq_code;
END;
'
LANGUAGE plpgsql
;

****************************

And here is the error message from phpPgAdmin:

SQL error:

ERROR: relation "e_mail_address_invisible_id_seq" already exists
CONTEXT: PL/pgSQL function "gen_sequences" line 45 at execute statement

****************************

Line 45 is the line that contains the execute statement.

Those are the raw facts.

My question is: Why didn't the chunk of "gen_sequences" code that consults
pg_catalog find a record of "e_mail_address_invisible_id_seq", and thereby
refrain from trying to create it again?

~ Thanks again
~ Ken