Thread: Dynamic DDL

create or replace function schema.insert_function(_schema text, _table
text) returns integer
as
$BODY$
declare
    _affected integer;
begin
    set search_path to _schema;
    insert into _table (columns) values(vals);
return 0;
end;
$BODY$ language plpgsql;

is there any whay to get the functionality of above to work with out
have to build a string and use the execute function?

On Mon, Apr 16, 2007 at 01:37:43PM -0700, Ketema wrote:
> create or replace function schema.insert_function(_schema text, _table
> text) returns integer

<snip>

>     set search_path to _schema;
>     insert into _table (columns) values(vals);

<snip>

> is there any whay to get the functionality of above to work with out
> have to build a string and use the execute function?

No.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Apr 16, 4:47 pm, klep...@svana.org (Martijn van Oosterhout) wrote:
> On Mon, Apr 16, 2007 at 01:37:43PM -0700, Ketema wrote:
> > create or replace function schema.insert_function(_schema text, _table
> > text) returns integer
>
> <snip>
>
> >    set search_path to _schema;
> >    insert into _table (columns) values(vals);
>
> <snip>
>
> > is there any whay to get the functionality of above to work with out
> > have to build a string and use the execute function?
>

> No.
>
> Have a nice day,
> --
> Martijn van Oosterhout   <klep...@svana.org>  http://svana.org/kleptog/
>
> > From each according to his ability. To each according to his ability to litigate.
>
>
>
>  signature.asc
> 1KDownload

Is there a technical reason why not?  I'd love to understand why.
This would be really usefull if it were possible.

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...

each column that i know can possibly be null I have to wrap in
coalesce so that the null does not cause the whole string to be null,
and therefore unable to be executed.  This is annoying and also causes
be to be unable to insert nulls and in some cases it would be nice to
have them.

Thanks

"Ketema" <ketema@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

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';

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?

On Apr 17, 11:19 am, Ketema <ket...@gmail.com> wrote:
> 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?

Please see http://pgsql.privatepaste.com/291tTsTeGp for a solutions I
came up with.  I would appreciate feedback.

Thanks