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... :-/
Here is excerpt from my proc that does the 'magic':
elseif operation='QUERY' then
/* Return list of all columns in table for single row with prim key
matching primarykeyvalue[] */
/* retrieve specified columns in our table */
/* iterate over array elements */
for i in array_lower(conf_field_list,1) ..
array_upper(conf_field_list,1) loop
sFieldNameOrig := conf_field_list[i];
sFieldAttr := conf_field_attr[i]; /*
coalesce(replace(substring(sFieldName from ':+(.*)?'),':',''),''); */
/* loop thru columns and execute SELECT for each one of them. Place
results in variable retval */
sFieldName := conf_field_list_vc[i]; /* fully qualified fieldname,
suitable for varchar casting */
/* raise notice '%',conf_sListSource[i]; */
begin
sListSource:=conf_sListSource[i];
exception when array_subscript_error then
end;
sListSource:=coalesce(sListSource,'');
/* in sListSource replace $USER_ID$ macro with our user's ID */
sListSource:=replace(sListSource,'$USER_ID$',get_effective_user());
/* raise notice 'fieldname % fieldtype % fieldattr
%',sFieldName,iFieldType,sFieldAttr; */
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;
execute sSQL INTO retval;
/* catch empty rowset */
get diagnostics rowcount=ROW_COUNT;
if ( rowcount = 0 ) then
retval.field_name:=sFieldNameOrig;
retval.field_caption:=coalesce(conf_field_capt[i],'');
retval.field_index:=0;
retval.field_value:='';
retval.field_listsource:=sListSource;
retval.field_type:=coalesce(conf_field_type[i],'');
retval.field_attr:=sFieldAttr;
end if;
return next retval;
end loop;
Peter
