Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 Apr 2001 23:41:36 -0000 1.9
+++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 22 Jul 2001 17:32:28 -0000 1.10
@@ -269,6 +269,8 @@
-- create or replace package body composition_rel
-- function new
+select define_function_args('composition_rel__new','rel_id,rel_type:composition_rel,object_id_one,object_id_two,creation_user,creation_ip');
+
create function composition_rel__new (integer,varchar,integer,integer,integer,varchar)
returns integer as '
declare
@@ -702,6 +704,8 @@
-- create or replace package body acs_group
-- function new
+select define_function_args('acs_group__new','group_id,object_type:group,creation_date:now(),creation_user,creation_ip,email,url,group_name,join_policy,context_id');
+
create function acs_group__new (integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,varchar,integer)
returns integer as '
declare
Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 29 Apr 2001 19:17:06 -0000 1.9
+++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 22 Jul 2001 17:32:28 -0000 1.10
@@ -63,6 +63,40 @@
end;' language 'plpgsql';
+-- Splits string on requested character. Returns requested element
+-- (1-based)
+
+create function split(varchar,char,integer)
+returns varchar as '
+declare
+ p_string alias for $1;
+ p_split_char alias for $2;
+ p_element alias for $3;
+
+ v_left_split integer;
+ v_right_split integer;
+ v_len integer;
+begin
+ v_len = length(p_string);
+ if v_len = 0 or p_string is null or p_element <= 0 then
+ return NULL;
+ end if;
+ if p_element = 1 then
+ v_left_split := 0;
+ else
+ v_left_split := instr(p_string, p_split_char, 1, p_element-1);
+ end if;
+ v_right_split := instr(p_string, p_split_char, 1, p_element);
+ if v_right_split = 0 then
+ v_right_split = v_len + 1;
+ end if;
+ if v_left_split = 0 and v_right_split = v_len+1 and p_element <> 1 then
+ return null;
+ end if;
+ return substr(p_string, v_left_split+1, (v_right_split - v_left_split - 1));
+end;' language 'plpgsql';
+
+
create function get_func_drop_command (varchar) returns varchar as '
declare
fname alias for $1;
@@ -480,3 +514,67 @@
end;' language 'plpgsql';
+-- PG substitute for Oracle user_tab_columns view
+
+create view user_tab_columns as
+ select upper(c.relname) as table_name,
+ upper(a.attname) as column_name,
+ upper(t.typname) as data_type
+ from pg_class c, pg_attribute a, pg_type t
+ where c.oid = a.attrelid
+ and a.atttypid = t.oid
+ and a.attnum > 0;
+
+-- Table for storing PL/PGSQL function arguments
+
+create table acs_function_args (
+ function varchar(100) not null,
+ arg_seq integer not null,
+ arg_name varchar(100),
+ arg_default varchar(100),
+ constraint acs_function_args_pk
+ primary key (function, arg_seq),
+ constraint acs_function_args_un
+ unique (function, arg_name)
+);
+
+
+-- Add entries to acs_function_args for one function
+-- Usage: select define_function_args('function_name','arg1,arg2:default,arg3,arg4:default')
+
+create function define_function_args(varchar,varchar)
+returns integer as '
+declare
+ p_function alias for $1;
+ p_arg_list alias for $2;
+
+ v_arg_seq integer default 1;
+ v_arg_name varchar;
+ v_arg_default varchar;
+ v_elem varchar;
+ v_pos integer;
+begin
+ delete from acs_function_args where function = p_function;
+
+ v_elem = split(p_arg_list, '','', v_arg_seq);
+ while v_elem is not null loop
+
+ v_pos = instr(v_elem, '':'', 1, 1);
+ if v_pos > 0 then
+ v_arg_name := substr(v_elem, 1, v_pos-1);
+ v_arg_default := substr(v_elem, v_pos+1, length(v_elem) - v_pos);
+ else
+ v_arg_name := v_elem;
+ v_arg_default := NULL;
+ end if;
+
+ insert into acs_function_args (function, arg_seq, arg_name, arg_default)
+ values (upper(p_function), v_arg_seq, upper(v_arg_name), v_arg_default);
+
+ v_arg_seq := v_arg_seq + 1;
+ v_elem = split(p_arg_list, '','', v_arg_seq);
+ end loop;
+
+ return 1;
+end;' language 'plpgsql';
+
Index: openacs-4/packages/acs-subsite/acs-subsite.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/acs-subsite.info,v
diff -u -r1.11 -r1.12
--- openacs-4/packages/acs-subsite/acs-subsite.info 29 May 2001 01:46:29 -0000 1.11
+++ openacs-4/packages/acs-subsite/acs-subsite.info 22 Jul 2001 17:31:29 -0000 1.12
@@ -81,6 +81,7 @@
+
@@ -156,6 +157,7 @@
+
Index: openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql 17 Apr 2001 04:10:06 -0000 1.2
+++ openacs-4/packages/acs-subsite/sql/postgresql/application-groups-create.sql 22 Jul 2001 17:31:29 -0000 1.3
@@ -126,6 +126,8 @@
-- return v_group_id;
-- end new;
+select define_function_args('application_group__new','group_id,object_type:application_group,creation_date:now(),creation_user,creation_ip,email,url,group_name,package_id,context_id');
+
create function application_group__new(integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,integer,integer)
returns integer as '
declare
Index: openacs-4/packages/acs-subsite/tcl/plpgsql-utility-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-subsite/tcl/plpgsql-utility-procs.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-subsite/tcl/plpgsql-utility-procs.tcl 22 Jul 2001 17:31:29 -0000 1.1
@@ -0,0 +1,56 @@
+ad_library {
+
+ Procs to help generate pl/pgsql dynamically
+
+ @author swoodcock@scholastic.co.uk
+ @creation-date Sun Jul 22 13:51:26 BST 2001
+ @cvs-id $Id: plpgsql-utility-procs.tcl,v 1.1 2001/07/22 17:31:29 stevew Exp $
+
+}
+
+namespace eval plpgsql_utility {
+
+ ad_proc -public generate_attribute_parameter_call {
+ { -prepend "" }
+ function_name
+ pairs
+ } {
+ Generates the arg list for a call to a pl/pgsql function
+
+ @author Steve Woodcock (swoodcock@scholastic.co.uk)
+ @creation-date 07/2001
+
+ } {
+ # Get the list of real args to the function
+ set real_args [db_list_of_lists get_function_args "
+ select arg_name, arg_default
+ from acs_function_args
+ where function = upper(:function_name)
+ order by arg_seq
+ "]
+
+ foreach row $pairs {
+ set attr [string trim [lindex $row 0]]
+ set user_supplied([string toupper $attr]) $attr
+ }
+
+ # For each real arg, append default or supplied arg value
+ set pieces [list]
+ foreach row $real_args {
+ set arg_name [lindex $row 0]
+ set arg_default [lindex $row 1]
+ if { [info exists user_supplied($arg_name)] } {
+ lappend pieces ":$user_supplied($arg_name)"
+ } else {
+ if { $arg_default == "" } {
+ lappend pieces "NULL"
+ } else {
+ lappend pieces "'[db_quote $arg_default]'"
+ }
+ }
+ }
+
+ return [join $pieces ","]
+ }
+
+}