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

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

-Jim

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

Collapse
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
[1] http://openacs.org/forums/message-view?message_id=4048911
[2] http://openacs.org/xowiki/Postgres_9

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

Collapse
Posted by Gustaf Neumann on
Tony,

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

many thanks,
-gustaf