populate_geometry_columns gives nonstandard use of \' in a string literal

[robe]

okay its just a warning so not a big deal. Just thought I'd mention it.

Also and maybe I'm asking for too much here or it doesn't make sense, since its something most people don't do but I have tables and views with combos of LINESTRING/MULTILINESTRING and POLYGONS/MULTIPOLYGON and populate_geometry_columns set them all to LINESTRING/POLYGON. Ideally I would think it would be better to say they are MULTIPOLYGON/MULTILINESTRING. Then again maybe not. Just thought I would throw that out there.

[mcayland]

Do we need to assign this one over to Kevin? The "nonstandard use of..." message needs to be fixed, but it should be relatively simple.

ATB,

Mark.

[kneufeld]

Sorry, I didn't see this ticket.

robe, I'm not sure I understand the problem. Can you please provide an example? I don't get the warning.

WRT the other comment about geometry type enforcement, the function detects the geometry type/srid/dimension by looking at the first geometry in the table and then tries to add the appropriate constraint to the column. These are the same constraints that would have been added had you used AddGeometryColumn?(). Are you saying that you are trying to mix geometry types in the same column? I don't think that's a standard use of PostGIS.

[robe]

Kevin -- sorry should have mentioned that after the fact -- yah its not worth the change I realized for this project I'm working on -- I had manually changed the constraints to allow a mix of MULTIPOLYGONS and POLYGONS since it was more efficient for the front end to deal with most as POLYGONS and there were a few MULTIPOLYGONS in there. So disregard that.

Regarding the nonstandard use -- this is what I get on 8.4. though I may nto be running the latest version on this box. This only shows if you have "escape_string_warning" set to on. Which at least on windows is the default install.

WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 68 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 102 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 146 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 148 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 150 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 198 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 200 PL/pgSQL function "populate_geometry_columns" line 52 at assignment WARNING: nonstandard use of \' in a string literal HINT: Use to write quotes in strings, or use the escape string syntax (E'...'). CONTEXT: string literal in PL/PgSQL function "populate_geometry_columns" near line 202

[robe]

Kevin,

if its any help. It appears to be the RAISE WARNINGs you have in populate_geometry_columns(oid) that are at fault. Strange I didn't even know those got flagged by this escape thing. Always thought those were limited to sql statements.

[robe]

Kevin,

This may be new questionable behavior in 8.4. I updated my populate_geometry_columns(oid) on my 8.4 beta2 install and it gives those errors just compiling the function.

However running the same on my 8.3 install gives me no such errors. and I verified I have escape warnings on in that.

We really should push leave this at 1.4.1 and possibly close it out after I verify with postgresql group that this is expected behavior.

[kneufeld]

Regina, Ok.. I'm trying to duplicate the the behaviour you are seeing but not having any luck ... if it's an 8.4 thing that would explain it.

As you suggest, we'll leave this for 1.4.1.

-- Kevin

[robe]

Okay I brought this up and it is intended new behavior -- notes from Tom Lane: from pg bug --  http://archives.postgresql.org/pgsql-bugs/2009-06/msg00045.php

Kevin if you want I can fix this one since I can test it or you can fix and I'll verify it works -- simple enough that we might as well do in 1.4.0 - so switching back.

---

This is an intended change: RAISE format strings are now processed the same as any other string literal. Personally I'd suggest using here rather than \' --- more standard, more future-proof, etc.

(Hmm, there seems to be nothing about it in the release notes ... not sure how we overlooked that.)

regards, tom lane

[robe]

fixed at r4152