Subject Re: Queries in UDFs
Author lutterot
Hi and thanks for the reply!

> >I know that such a functionality could be achieved by writing a stored
> >procedure. But it is not possible to use stored procedures that return
> >scalars in the same manner as functions, right?
> Well, it is possible. You can write an executable SP that returns a
single output field. It's not a function, though it really depends
how you want to use it.
> But the normal way to return a scalar field in a set is simply to
use a correlated subquery expression in your dynamic query.
> Provide an example of what you want to achieve so that people can
share some of their favourite tricks with you. :-)

I need something along these lines:

CREATE TABLE substitution (x integer, y integer);

CREATE PROCEDURE substitute (x integer)
RETURNS (y integer)
SELECT y FROM substitution WHERE x=:x INTO :y;

Because the procedure uses a substitution table, the way the numbers
are substituted can be changed easily in the DB. That's what I need.
And I was wondering if there is an easy way to use it similar to this:

CREATE TABLE somenumbers (n integer);

INSERT INTO somenumbers VALUES (substitute(1));