Re: Dynamic DDL - Mailing list pgsql-general

On Apr 17, 7:35 am, Ketema <ket...@gmail.com> wrote:
> On Apr 16, 6:24 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>
>
>
> > "Ketema" <ket...@gmail.com> writes:
> > > I have an example were I have to build a string in the below manner:
> > > values (' || new.tpv_success_id || ',''' || new.order_date || ''',' ||
> > > new.tpv_id || ',' || new.ver_code || ',''' || new.agent_name || ''','
> > > || new.agent_id || ','
> > >            || new.acct_id || ',''' || new.first_name || ''',''' ||
> > > new.last_name || ''',''' || new.ssn || ''',''' ||
> > > coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> > > || ''',''' || coalesce(new.spouse_ssn,'') || ''',''' etc...
>
> > This looks to me like you're simply willfully ignoring the easy path.
> > There's nothing there that wouldn't work just as well without EXECUTE,
> > viz
>
> > values (new.tpv_success_id, new.order_date, new.tpv_id, new.ver_code,
> > new.agent_name, new.agent_id, new.acct_id, new.first_name,
> > new.last_name, new.ssn, new.dl_number, new.spouse_name, new.spouse_ssn,
> > etc...
>
> >                         regards, tom lane
>
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
> I am sorry for being lazy Tom....This part of the dynamix statement
> your right is simple and would work.
>
> ITs actuall in the the beginning....  INSERT INTO _dynamic_table....
> Its substituting the table name for the insert that does not work and
> I was wondering the technical reasons behind that.  I had thought
> about a rule, but on a rule the table has to already exist in order to
> do an instead of insert.  My purpose is I am trying to implement
> partitioning.  I included the full trigger function below.  The
> function does work as is, my only complaint is that on the columns I
> have to coalesce i get '' (null string) inserted instead of an actual
> null and this has made me have to make some columns text or varchar
> instead of numeric or other data types. (for example cancel_date
> should be a date type, but if this comes thorugh as null i have to
> coalesce it or the whole string becomes null, and '' is not a valid
> date type so I had to make the table column a varchar)
>
> CREATE OR REPLACE FUNCTION frontier.order_details_partitioner()
>   RETURNS "trigger" AS
> $BODY$
> declare
>         _month text;
>         _year text;
>         _schema text;
>         _table text;
>         _table_exists text;
>         _sql text;
> begin
>         _month := (select trim(to_char(new.order_date, 'month')));
>         _year := (select trim(to_char(new.order_date, 'yyyy')));
>         _schema := 'frontier';
>         _table := 'order_details_' || _month || '_' || _year;
>         _table_exists := (select schemaname || '.' || tablename from
> pg_tables
>                 where schemaname = _schema and tablename = _table);
>         if _table_exists is null then
>                 _sql := 'create table ' || _schema || '.' || _table || ' (CONSTRAINT
> "C_partition_rule" CHECK ( trim(to_char(order_date, ''month'')) = '''
> || _month || ''' and trim(to_char(order_date, ''yyyy'')) = '''
>                 || _year || ''')) inherits (frontier.order_details);';
> raise notice '%', _sql;
>                 execute(_sql);
>         end if;
>         _sql := 'insert into ' || _schema || '.' || _table ||
> ' (tpv_success_id, order_date, tpv_id, ver_code,
>             agent_name, agent_id, acct_id, first_name, last_name, ssn,
> dl_number,
>             spouse_name, spouse_ssn, day_phone, evening_phone,
> svc_address,
>             svc_city, svc_state, svc_zip, billing_address,
> billing_city,
>             billing_state, billing_zip, order_number, order_status,
> provisioned_date,
>             promotion, products, data_requirement_titles,
> data_requirement_values, cancel_date,
>             cancel_note, issue_notes, has_dish, has_dish_billing_info,
> dish_order_number,
>             dish_install_date, dish_customer_contacted, personnel_id,
> call_id,
>            marketer_division_id, existing_status, app_id) values (' ||
> new.tpv_success_id || ',''' || new.order_date || ''',' || new.tpv_id
> || ',' || new.ver_code || ',''' || new.agent_name || ''',' ||
> new.agent_id || ','
>                 || new.acct_id || ',''' || new.first_name || ''',''' ||
> new.last_name || ''',''' || new.ssn || ''',''' ||
> coalesce(new.dl_number,'') || ''',''' || coalesce(new.spouse_name, '')
> || ''',''' || coalesce(new.spouse_ssn,'') || ''','''
>                 || new.day_phone || ''',''' || coalesce(new.evening_phone,'') ||
> ''',''' || new.svc_address || ''',''' || new.svc_city || ''',''' ||
> new.svc_state || ''',''' || new.svc_zip || ''',''' ||
> new.billing_address || ''',''' || new.billing_city || ''','''
>                 || new.billing_state || ''',''' || new.billing_zip || ''',''' ||
> coalesce(new.order_number,'') || ''',''' || new.order_status ||
> ''',''' || coalesce(new.provisioned_date,'') || ''',''' ||
> coalesce(new.promotion,'') || ''',''' || coalesce(new.products,'') ||
> ''',''' || coalesce(new.data_requirement_titles,'') || ''','''
>                 || coalesce(new.data_requirement_values,'') || ''',''' ||
> coalesce(new.cancel_date,'') || ''',''' ||
> coalesce(new.cancel_note,'') || ''',''' ||
> coalesce(new.issue_notes,'') || ''',' || case when new.has_dish is
> true then 'true' else 'false' end || ',''' ||
> new.has_dish_billing_info || ''',''' ||
> coalesce(new.dish_order_number,'') || ''','''
>                 || coalesce(new.dish_install_date, '') || ''',''' ||
> new.dish_customer_contacted || ''',' || new.personnel_id || ',' ||
> new.call_id || ',' || new.marketer_division_id || ',''' ||
> coalesce(new.existing_status, '') || ''',' || new.app_id || ');';
> raise notice '%', _sql;
>         execute(_sql);
>         return null;
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION frontier.order_details_partitioner() OWNER TO
> all_users;
> COMMENT ON FUNCTION frontier.order_details_partitioner() IS 'This
> function redirects inserts into order_details into the appropriate
> child table, creating it if necessary.  Child tables are kept my
> month_year ex:
> order_details_april_2007';

One of the other problems I have with building a string for execution
is that certain data types have no operator to concantenate to a
string.  Array for example.  Even though when you select and array you
get a nice array notation {val, "quoted val", val} and if you put
single quotes around the same thing pg happily understands that it is
an array.  Yet try to concatenate a varchar[] column in a function
such as mine and you get:

operator does not exist: text || character varying[]

and you can't explicitly cast and array column to text or varchar
either.  Does any one have a custom operator that wlll do that?