Re: Weird double single quote issue

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