Peter wrote:
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.
--
Richard Huxton
Archonet Ltd
