php - PostgreSQL: creating several pgplsql procedures in a batch -

i have php-script several similar sql tables (only 1 column varies):

define('sql_tables', ' create table hide_id (         id       varchar(20) references quincytrack,         username varchar(20) not null,         updated  timestamp default current_timestamp ); create table hide_name (         name     varchar(20) not null,         username varchar(20) not null,         updated  timestamp default current_timestamp ); create table hide_category (         category varchar(120) not null,         username varchar(20) not null,         updated  timestamp default current_timestamp ); create table hide_appsversion (         appsversion varchar(30) not null,         username varchar(20) not null,         updated  timestamp default current_timestamp ); create table hide_osversion (         osversion varchar(30) not null,         username varchar(20) not null,         updated  timestamp default current_timestamp ); 

and i'd create upsert procedure each of them:

define('sql_procs', ' create or replace function hide_id_upsert(         _id       varchar,         _username varchar         ) returns void $body$         begin                 update hide_id set                         id       = _id,                         username = _username,                         updated  = current_timestamp                 id = _id;                  if not found                         insert hide_id (                                 id,                                 username                         ) values (                                 _id,                                 _username                         );                 end if;         end; $body$ language plpgsql; '); 

i wonder, if in batch pgplsql or php means. like:

$columns = array('id', 'name', 'category', 'appsversion', 'osversion'); foreach ($columns $key) {    $sth = $pg->prepare(sprintf(sql_procs_template, $key, $key, ...);    $sth->execute(); } 

but without above ugliness of having specify $key numerous times.

using postgresql 8.4.5 , php 5.1.6 under centos 5.5 linux

thank you! alex

well, don't see using %s anywhere in string sprintf, if want use same variable multiple times in same sprintf string, can use %1$s first argument, this:

sprintf('%1$s %1$s', 'hubba') // hubba hubba 

edit: here's link: http://php.net/manual/en/function.sprintf.php. of course, not postgrespecific, hope you're looking for.

edit: so, in case, do:

define('sql_procs', ' create or replace function %1$s_upsert(         _id       varchar,         _username varchar         ) returns void $body$         begin                 update %1$s set                         id       = _id,                         %2$s = _%2$s,                         updated  = current_timestamp                 id = _id;                  if not found                         insert %1$s (                                 id,                                 %2$s                         ) values (                                 _id,                                 _%2$s                         );                 end if;         end; $body$ language plpgsql; ');  $columns = array('id', 'name', 'category', 'appsversion', 'osversion'); foreach ($columns $key) {    $sth = $pg->prepare(sprintf(sql_procs, 'hide_' . strtolower($key), $key));    $sth->execute(); }