Forum OpenACS Development: Re: Dynamic plpgsql package/function creation.

Posted by Jim Lynch on
No, the backslashes for the $ chars should not be presented to postgres at all.

As you may know, the dollar quoting can be simply $$ (with $$ to end the quote) or $sometext$, with $sometext$ to end.

Then, the ' quoting can again be used just for strings, as in 'foo' -- instead of ''foo'' which used to be required when the entire function bodies were enclosed in ' ... '.

Where does openacs initially produce the \$\$?


Posted by Tony Kirkham on
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.

Posted by Gustaf Neumann on
Tony, can it be that you are using aolserver and that you have not upgraded nspostgres in the last two years? See e.g. [1] and [2]

all the best
-gustaf neumann

Posted by Tony Kirkham on
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 $$
[plpgsql_utility::generate_attribute_parameters $attribute_list];
v_$id_column ${table_name}.${id_column}%TYPE;
@@ -230,11 +230,11 @@
return v_$id_column;

-\$\$ 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 $$

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

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

return null;



Posted by Gustaf Neumann on

you are right, the dollar-escaping was incorrect. fixed in the oacs-5-8 branch.

many thanks,