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'
---------------------------------------------------------