Re: how to traverse a bytea value in pl/pgsql

On 18 Dec 2002 at 10:30, Jules Alberts wrote:
<snip>
> The selection works fine, but now I have to find a way to traverse
> tgargs. It's of the datatype "bytea" (which sounds C-ish, but I'm not a
> C programmer) and has a value like:
>
>   <unnamed>\000cust\000land\000UNSPECIFIED\000land\000code
>
> I'm looking for something like this (in a sort of semi-code):
>
>     SEPERATOR := ''\000'';
>   FOR (i := 1; i <= rs.tgnargs; i++) {
>     raise notice ''%'', byteaslice(rs.tgargs, SEPERATOR, i);
>   }
>
> I experimented with functions like byteacat(), strpos(), substr() etc.,
> but none does what I want. Can anybody tell me how to do this? Thanks a
> lot IA!

I managed to make something that does the job. The biggest problem was
that after converting the bytea to text, strpos(tgargs_as_text,
''\\000'') allways returns 1, no matter what the actual position is. So
I looked for '000' instead, which could be dangerous in case a table or
column contains this string. So here it is: the workaround.

I will send this in to the Ugliest Workaround Of All Times Contest and
probably win a Ferrari. If anyone has a better idea, please tell me
(after the Ferrari has been shipped). TIA!

---------------------------------------------------------
create function getreftable(text, text) returns text as '
declare
  TABL alias for $1;
  COLM alias for $2;
  rs RECORD;
  tgarg text;
  pos int;
  tab_1 text;
  col_1 text;
  tab_2 text;
  col_2 text;
  ret text;
begin
  ret := '''';
  for rs in select tgnargs, tgargs from pg_trigger join pg_class
    on tgrelid=pg_class.oid
    where tgisconstraint = true and relname = TABL loop
    tgarg := rs.tgargs;
    pos   := strpos(tgarg, ''000'');
    tgarg := substring(tgarg, pos + 3);
    pos   := strpos(tgarg, ''000'');
    tab_1 := substring(tgarg, 1, pos -2);
    tgarg := substring(tgarg, pos + 3);
    pos   := strpos(tgarg, ''000'');
    col_1 := substring(tgarg, 1, pos -2);
    tgarg := substring(tgarg, pos + 3);
    pos   := strpos(tgarg, ''000'');
    tgarg := substring(tgarg, pos + 3);
    pos   := strpos(tgarg, ''000'');
    tab_2 := substring(tgarg, 1, pos -2);
    tgarg := substring(tgarg, pos + 3);
    pos   := strpos(tgarg, ''000'');
    col_2 := substring(tgarg, 1, pos -2);
    -- check if this is the one
    if lower(tab_1) = lower(TABL) and lower(col_1) = lower(COLM) then
      ret := tab_2 || ''.'' || col_2;
      exit;
    end if;
  end loop;
  return ret;
end;
' language 'plpgsql'
---------------------------------------------------------