Thread: Getting closer with functions, but...

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

Scott Holmes  <sholmes@pacificnet.net> writes:
> 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).

Not in plpgsql, because it wants to precompile the queries, so you can't
determine table or field names at runtime, just substitute values for
constants.

pltcl doesn't precompile, so it should do what you want (at a price in
performance of course).  I think plperl works like pltcl, if you prefer
perl over tcl.

            regards, tom lane

At 20:58 12/07/00 -0700, Scott Holmes wrote:
>
>This function works but is not selective enough.  The tg_argv[] are pointers
>to the arguments passed.  And they do work correctly.
>
...
>
>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).

Writing it in C will enable you to build SQL statements based on pretty
much anything, if that helps. The stuff in contrib/spi & contrib/ri_?? has
some basic and some more complex examples. The manual section on triggers
also includes a useful example.






----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/