Forum OpenACS Development: Dynamic plpgsql package/function creation.

When working with OpenACS 5.8.0 with PostgreSQL 9 I have found a few places where dynamically generated plpgsql functions are failing due to backslashed dollar sign quoting (\$\$) in the function definitions.

An example of this is in the group type generator. The package_generate_body.body partialquery in the package-procs-postgresql.xql file in the acs-subsite package contains the code to generate plpgsql functions, new and delete, for new group types. The query text contains the function definitions quoted by \$\$. This causes the group_type::new tcl proc to fail. If I remove the backslashes leaving the quoting as $$, the call works fine.

Is there a reason for or a case where these queries need this backslashing that I am not seeing? I would like to make these fixes but don't want to trade one bug for another.

Thanks,
-Tony

I am seeing the \$\$ in the package_generate_body.body partial query in the package-procs-postgresql.xql file in the acs-subsite package.

This is the only place I can currently find the problem.

Sorry, it has been a while since I have been able to work on this.
The issue that I am referring to has to do with particular code in the file /packages/acs-subsite/tcl/package-procs-postgresql.xql.
I do not believe this has anything to do with the nspostgres. As I explained above, I would like to know if there is a reason for this escaped quoting. When I run it as is it fails. With my changes to remove the escaping it runs fine.

A universal diff is provided below to show the changes I am asking about.

--- /web/vendor/openacs-5.8.0/packages/acs-subsite/tcl/package-procs-postgresql.xql Sat Mar 30 13:32:40 2013
+++ package-procs-postgresql.xql Fri Aug 1 17:45:10 2014
@@ -209,7 +209,7 @@
perform define_function_args('${package_name}__new','[plpgsql_utility::define_function_args $attribute_list]');

CREATE FUNCTION ${package_name}__new([plpgsql_utility::generate_function_signature $attribute_list])
-RETURNS [plpgsql_utility::table_column_type ${table_name} ${id_column}] AS \$\$
+RETURNS [plpgsql_utility::table_column_type ${table_name} ${id_column}] AS $$
DECLARE
[plpgsql_utility::generate_attribute_parameters $attribute_list];
v_$id_column ${table_name}.${id_column}%TYPE;
@@ -230,11 +230,11 @@
return v_$id_column;

END;
-\$\$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql;

CREATE FUNCTION ${package_name}__delete (
p_${id_column} [plpgsql_utility::table_column_type ${table_name} ${id_column}]
-) RETURNS integer AS \$\$
+) RETURNS integer AS $$
DECLARE
BEGIN

@@ -242,7 +242,7 @@
return 1;

END;
-\$\$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql;

return null;
end;

Thanks,

-Tony