Search Postgresql Archives

Re: Weird double single quote issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux