Now, the weirdest thing is that for some db users everything works
as it should, but for others the returned string is with TWO single
quotes!
select 'string to be stored with '' quotemark'::text as
returned_string
returns
string to be stored with '' quotemark
I cannot replicate the same behavior from SQL Editor, or psql
commandline. It only happens inside my proc (which is rather
monstrous).
1. Have you done ALTER ROLE ... SET ... on any users?
2. Are the locales/encodings different for the users?
3. Can you isolate the problem part of the procedure?
I'm guessing you'll have to do #3 since you can't reproduce it with
psql. If I were you, I'd suspect the proc rather than the DB in this
case.
1. No
2. No. In fact I can replicate the problem on the same computer using
PgAdmin (connecting as two different users)
3. I can isolate the part but it does not help much. I'm even doing
RAISE NOTICE and printing out the actual SQL statement that is being
executed... and it's IDENTICAL for both users yet returns different
values. Executing exactly the same SQL statement from commandline
produces identical results for both users.
4. I'm totally baffled... :-/
[snip]
sSQL := $$select '$$ || sFieldNameOrig || $$'::varchar as
field_name, $$ ||
coalesce(conf_field_capt[i],'')::text || $$::text as
field_caption, $$ ||
$$''::varchar as field_index, $$ ||
sFieldName::varchar || $$ as field_value, $$ ||
$$'$$ || sListSource::text || $$'::text as field_listsource, '$$
|| coalesce(conf_field_type[i],'') || $$'::int as field_type, $$ ||
$$'$$ || sFieldAttr || $$'::text as field_attr $$ ||
$$ from "$$ || conf_table_name || $$" $$ || sWhere;
raise notice 'executing %',sSQL;
I assume you're doubling the quotes in these variables earlier in the
function. I'd tidy this up via quote_literal:
sSQL := 'select ' || quote_literal(sFieldNameOrig) || '::varchar as
field_name...'
There's a quote_ident() function too - details in the "string
functions" section of the manuals.
Allright, the quote_literal() function helped to a certain extent. One
field is now always properly formatted, and one other sometimes is
quoted... and sometimes is not.
It still feels like internal PG issue to me... we'll try upgrading to
latest revision and see what happens...
thanks for the help!
Peter
