Executing Stored Routine

I need to build the name of the stored routine I would like to call based on input value passed in. Example: LET query_return = "execute function sel_dsdt_" || datatype_code || ";" In this example "datatype_code" is passed to me, and I want to concatenate this to my procedures prefix. As all the stored procedures begin with "sel_". What is the best way to accomplish this task? Any idea? Appreciate your help. Thanks in advance. Russell L. Cook Marconi Integrated Systems, Inc (858) 592-5008 russell.cook@Marconi Integrated Systems, Inc

Cook, Russell L (Russell.Cook@marconi-is.com) wrote: : : I need to build the name of the stored routine I would like to call based on : input value passed in. : : Example: LET query_return = "execute function sel_dsdt_" || datatype_code || : ";" : : In this example "datatype_code" is passed to me, and I want to concatenate : this to my procedures prefix. As all the stored procedures begin with : "sel_". : : What is the best way to accomplish this task? Any idea? Appreciate your : help. Thanks in advance. : Erm . . . . It shouldn't matter. The engine over-loads the functions. -- -- OK. First, let's create a few new types. -- CREATE DISTINCT TYPE Foo AS INTEGER; GRANT USAGE ON TYPE Foo TO PUBLIC;-- CREATE DISTINCT TYPE Bar AS INTEGER; GRANT USAGE ON TYPE Bar TO PUBLIC;-- -- Now, the engine understands that these are different things, -- and when it goes to execute a function, it will use the -- data types to figure out which function to fire. To illustrate -- this, I've created the following pair of functions. -- CREATE FUNCTION Mug ( Arg1 Foo ) RETURNING INTEGER RETURN (Arg1::INTEGER + 1);END FUNCTION; GRANT EXECUTE ON FUNCTION Mug(Foo) TO PUBLIC;-- CREATE FUNCTION Mug ( Arg1 Bar ) RETURNING INTEGER RETURN (Arg1::INTEGER - 1 );END FUNCTION; GRANT EXECUTE ON FUNCTION Mug ( Bar ) TO PUBLIC;-- -- This is the 'switch'. MugWump() is simply here to illustrate -- the point. The casting in the invocation of Mug() makes the -- engine pick the appropriate function to call. -- CREATE FUNCTION MugWump ( Arg1 INTEGER, Arg2 LVARCHAR ) RETURNING INTEGER; IF ( Arg2 = 'Foo' ) THEN RETURN Mug ( Arg1::Foo ); END IF; RETURN Mug ( Arg1::Bar ); END FUNCTION; GRANT EXECUTE ON FUNCTION MugWump ( INTEGER, LVARCHAR ) TO PUBLIC;-- -- For instance . . . -- EXECUTE FUNCTION MugWump ( 3, 'Foo' ); EXECUTE FUNCTION MugWump ( 3, 'Bar' );-- -- Hygiene. -- DROP FUNCTION MugWump ( INTEGER, LVARCHAR ); DROP FUNCTION Mug ( Bar ); DROP FUNCTION Mug ( Foo );DROP TYPE Foo RESTRICT; DROP TYPE Bar RESTRICT; One way to look at this is to say that there is no need to include a 'type_name' in the function's name. It's already there, implicitly, because the 'name' of a function -- called its signature -- consists of the function's name and its argument type vector. -- ===================================================================== Paul Brown ^..^ pbrown@postgres.Berkeley.EDU (oo) - Oink! #include <std_disclaimer.h> "Think global - act loco!" - Zippy the Pinhead =====================================================================