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

Hello everyone,

I'm working on a function that will give me the table + column another
table + column is referenced to. The function looks like this:

---------------------------------------------------------
create function getreftable(text, text) returns text as '
declare
  TABL alias for $1;
  COLM alias for $2;
  rs RECORD;
begin
  for rs in select tgnargs, tgargs from pg_trigger join pg_class
    on tgrelid=pg_class.oid
    where tgisconstraint = true and relname = TABL loop
    -- check out tgargs
  end loop;
  return ''FIXME'';
end;
' language 'plpgsql'
---------------------------------------------------------

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!

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

"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> I'm working on a function that will give me the table + column another
> table + column is referenced to. The function looks like this:

Update to 7.3 and look in pg_constraint, instead...

            regards, tom lane

On 18 Dec 2002 at 10:47, Tom Lane wrote:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> > I'm working on a function that will give me the table + column another
> > table + column is referenced to. The function looks like this:
>
> Update to 7.3 and look in pg_constraint, instead...

aaaarrrrghh!

>             regards, tom lane

Just kidding :-). Thanks for the tip, I allways prefer using built-in
functionality rather than homemade. Much more portable.

Now on to installing pg 7.3. Too bad there aren't any redhat 7.3 RPMs
yet. Anyway, thanks again.