Getting closer with functions, but...

Поиск
Список
Период
Сортировка
От Scott Holmes
Тема Getting closer with functions, but...
Дата
Msg-id 200007130358.UAA15337@scotts.mynetwork.net
обсуждение исходный текст
Ответы Re: Getting closer with functions, but...  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Getting closer with functions, but...  (Philip Warner <pjw@rhyme.com.au>)
Список pgsql-general
This function works but is not selective enough.  The tg_argv[] are pointers
to the arguments passed.  And they do work correctly.

CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
declare
  fname text;
  rkey text;
  BEGIN
    fname := tg_argv[0];
    rkey := ''old.'' || tg_argv[1];
    delete from stxnoted where record_key = old.wher;
    return old;
  END;'
LANGUAGE 'plpgsql';

What I need can be accomplished with a rather massive if/then series:

if fname = "location" then
  delete from stxnoted where filename = "location" and record_key = old.wher;
end if

if fname = "events" then
  delete from stxnoted where filename = "events" and record_key = old.evntnmbr;
end if

... ( maybe 100 different tables )...

It seems the sql statement needs old.field or new.field to provide a value.
Unfortunately, my first value, fname, is not a field value but a table name
and my second value may have one of several field names.

Is there a syntax that will allow me to build an sql statement for use in
theis funcation (ie delete from stxnoted where filename = fname and record_key
= rkey).

Thanks, Scott




В списке pgsql-general по дате отправления:

Предыдущее
От: Joseph Shraibman
Дата:
Сообщение: Re: select for update not locking properly.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Getting closer with functions, but...