Quick LLBLGen 3.0 question around stored procedures (actually functions in PostgreSQL)

I've a table storing hierarchical data. I'm using adapter.

I've created a function which takes the ID of an entity and returns all "children" recursively (there may be a way to create a common table expression for PostgreSQL in LLBL already but I can't find it so I had to make a function).

I'm wanting to use the function in a where clause, basically:

...WHERE area_id IN (SELECT id FROM function_recursive_areas(1))

either this or a join would work too I guess

...JOIN function_recursive_areas(1) AS b ON a.area_id = b.id

You can use Database Function Calls http://www.llblgen.com/documentation/3.0/LLBLGen%20Pro%20RTF/hh_start.htm to achieve this.

Matt

..however, you can't use a DBFunctionCall as a queryable resulset. So, you can use DBFunctionCalls in predicates, expressions, etc, but no in FROM clause. If the query/function is too complex to reproduce it in LLBLGen classes I would recommend to use a Stored Procedure to fetch the whole thing.

In most cases it would be possible to rewrite the query so the function doesn't represent a resulset but a scalar.

This is currently not supported: it's not possible to specify the source of a select to be a function, unfortunately.

So what you should do is creating your own predicate class. I'd derive the predicate class from FieldCompareRangePredicate, and override the ToQueryText to specify the function with the value hardcoded. But it's up to you of course how flexible you want it to be.

Then in your own code, create an instance of that predicate to have the proper where clause.

Otis wrote:

This is currently not supported: it's not possible to specify the source of a select to be a function, unfortunately.

So what you should do is creating your own predicate class. I'd derive the predicate class from FieldCompareRangePredicate, and override the ToQueryText to specify the function with the value hardcoded. But it's up to you of course how flexible you want it to be.

Then in your own code, create an instance of that predicate to have the proper where clause.

Cheers Frans

That sounds like the most elegant solution anyways...