Re: Function Calling Using OIDS - Mailing list pgsql-general

Yes, you can call stored procedures (or functions) indirectly.

Here's an example.  It assumes that it knows what parameter types your
dynamic function is expecting.

create table tst(
 id numeric,
 func text,
 res numeric
);

create or replace function double_the_id(numeric) returns numeric as '
begin
 RETURN $1 * 2;
end;'
language 'plpgsql';

create or replace function tf_tst() RETURNS OPAQUE AS '
DECLARE
 dpsql text;
 dprec record;
BEGIN
 dpsql := \'select \'||NEW.func||\'(\'||num2text(NEW.id)||\') as res;\';
 for dprec in execute dpsql loop
  NEW.res := dprec.res;
 end loop;
 RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER ti_tst BEFORE INSERT ON tst
FOR EACH ROW EXECUTE PROCEDURE tf_tst();

insert into tst(id, func) values (1, 'double_the_id');
insert into tst(id, func) values (2, 'double_the_id');
insert into tst(id, func) values (3, 'double_the_id');
insert into tst(id, func) values (4, 'double_the_id');

select * from tst;

drop table tst;
drop function double_the_id(numeric);
drop function tf_tst();

num2text is just a hoagy function i whipped up to convert type numeric to
type text.  I hope this helps.

Mark

----- Original Message -----
From: "Evert Carton" <evertcarton@netscape.net>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, October 23, 2002 3:30 AM
Subject: [GENERAL] Function Calling Using OIDS


> Hi all,
>
> I am wondering, ...
>
> Is there a way to call a stored procedure indirectly, if you have it's
> name or it's oid in the pg_proc table ?
>
> Or ...
>
> What I'm trying to achieve ...
>
> I'd like to add a reference to a stored procedure in a record, (e.g.
> the OID, but more likely the name, since OID's can change), and to
> have this function called by triggers when the record using that
> reference in any way, directly or indirectly, is being
> added/updated/deleted.
> The function being called should be different for each record.
> I could of course write one big one, but it looks like the functions I
> need might be written in different languages. ...
>
> Any ideas ?
>
> Thx
>
> Evert Carton
> evertcarton@netscape.net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>