Subject Re: Queries in UDFs
Author Adam
--- In, "lutterot" <lutteroth@...> wrote:
> Hi and thanks for the reply!
> > >I know that such a functionality could be achieved by writing a
> > >procedure. But it is not possible to use stored procedures that
> > >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;
> END;
> 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));

Hello Christof,

This should be reasonably straight forward then, because you already
have a selectable stored procedure that returns exactly one row.

INSERT INTO somenumbers (n) select y from substiture(1);