1

How do I generically translate a subselect into a pipelined function call?

For example how would I translate this:

select id, stuff from t1 where id in (select unique id from kw where k = 'foo')

to this:

select id, stuff from t1 where id in (select id from table(has_kw('foo'))
MT0
  • 143,790
  • 11
  • 59
  • 117
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
  • Are both of those queries supposed to be `id in (subquery)` rather than `id = (subquery)`? So you're just asking how to write the pipelined table function? – Justin Cave Jan 23 '15 at 00:56

1 Answers1

4

Writing the pipelined table function is relatively straightforward

CREATE TYPE num_tbl AS TABLE OF NUMBER;

CREATE FUNCTION has_kw( p_k IN VARCHAR2 )
  RETURN num_tbl
  PIPELINED
IS
BEGIN
  FOR i IN (SELECT DISTINCT id FROM kw WHERE k = p_k)
  LOOP
    PIPE ROW( i.id );
  END LOOP;
  RETURN;
END;

Now, I'm not sure that it would really make a whole lot of sense to use a pipeliend table function here. But perhaps your actual use case is more complicated and a pipelined table function would be more appropriate.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thanks Justin... you're right, my application is more complicated. This is a perfect minimal example. All other examples I could find also created a row type, and had more gratuitous processing in the body of the function. – Mark Harrison Jan 23 '15 at 01:43