Thread: Pass where clause to a function

On 8/13/2014 10:08 PM, Andrew Bartley wrote:
>
> Is it possible to create a view or foreign table that will do
> something like this
>
> select * from table_x
> where x_id = 10;
>
> passing the where "x_id = 10" to a function
>
> sorta like this
>
> select * from api_function('x = 10')
>
> or
>
>  select * from api_function(10)
>
> passing the result set back to the original select....
>

your first version of api_function would have to use that x=10 to
construct the query and EXECUTE it, then return the recordset,

your second version of the view would just pass 10 in as an argument,
which could be used for the query select * from table_x where x_id=$1

either way, your view would be select * from api_function(whichever).

but I think you're rather confused here, as I don't see much utility in
either of these constructs in the form you specified.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

I need this because it is a customer requirement.  The underlying tables the api will query are dynamically created, they are period/content partitioned and distributed across multiple servers and database types... not just postgres.  The api/function will determine what servers/databases/tables (some of them are foreign tables) to query based on the parameters passed to the api.  The customer wants to use their current reporting tool that does not support function selects.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 14 Aug 2014, at 7:59, Andrew Bartley <ambartley@gmail.com> wrote:

> I need this because it is a customer requirement.  The underlying tables the api will query are dynamically created,
theyare period/content partitioned and distributed across multiple servers and database types... not just postgres.
Theapi/function will determine what servers/databases/tables (some of them are foreign tables) to query based on the
parameterspassed to the api.  The customer wants to use their current reporting tool that does not support function
selects.

Normally the reporting tool handles that sort of thing. I use a couple of reporting tools for work (WebFOCUS and some
BO),both of which have an abstraction layer that places table definitions in specific databases. I haven’t come across
anythat couldn’t handle functions, so I’m curious what kind of reporting tool your customer is using that it can’t?
Perhapsthey need something more capable (in which case I wouldn’t advise BO)? 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Thu, Aug 14, 2014 at 1:17 AM, John R Pierce <pierce@hogranch.com> wrote:
> On 8/13/2014 10:59 PM, Andrew Bartley wrote:
>
> I need this because it is a customer requirement.  The underlying tables the
> api will query are dynamically created, they are period/content partitioned
> and distributed across multiple servers and database types... not just
> postgres.  The api/function will determine what servers/databases/tables
> (some of them are foreign tables) to query based on the parameters passed to
> the api.  The customer wants to use their current reporting tool that does
> not support function selects.
>
>
> I don't know how you would pass those expression fragments to a view to be
> passed to the function, however.
>
> what you describe above (and in the rest of your post) sounds very
> convoluted, and rather prone to poor performance.

Also be advised that sending user generated raw text to an EXECUTE'd
statement is a horrible injection risk.  It's still ok (somtimes) but
only in a 100% trust situation.  Perhaps you could add a little bit of
safety by adding making two arguments: comparison field and comparison
value and using quote_ident/quote_literal around them; it's a good
habit to use quote_xx around any token that is possible to be supplied
by a human.

merlin