It is like the weirdest thing ever...
I have a proc that dynamically generates SQL, executes it and returns
results as setof record. Some of fields are strings with single quotes
inside them. Since these strings are being picked up from database I
store them as:
'string to be stored with '' quotemark'
which is being inserted in generated SQL string as:
select 'string to be stored with '' quotemark'::text as returned_string
and gets returned to user as:
string to be stored with ' quotemark
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).
The behavior is consistent across users (if it returns two single-quotes
instead of one it keeps doing it for the same user). However the
behavior is NOT consistent across different examples: some strings are
returned with single quote, others always get two single quotes.
I can't put my finger on exactly what's causing such behaviour, but gut
feeling is that it's got something to do with other fields and their
values that are being returned in the same SELECT statement. Sample SELECT:
select 'RCCU'::varchar as field_name, 'RCCU'::text as field_caption,
''::varchar as field_index, coalesce("RCCU"::varchar,'')::varchar as
field_value, 'select "CU" as item_id,"CUNAME" as item_data from
prf_import_culst where user_id=get_effective_user() order by
"CUNAME"'::text as field_listsource, '1043'::int as field_type, '<?xml
version="1.0" encoding="utf-8"?>
<field_attr></field_attr>
'::text as field_attr from "prf_import_rcrds" where import_id like '%'
and user_id=get_effective_user() and is_visible
Owner user (the one who owns the proc) always gets expected behaviour
from the proc (single quote instead of two single quotes). Other users
(all or nearly all) get two single-quotes on some strings (always the
same strings).
We're running Postgres 8.1.3 FWIW
Any ideas?
Peter
