Postgres Pro
Downloads
Home   >   mailing lists

Re: Pass where clause to a function - Mailing list pgsql-general

From Andrew Bartley
Subject Re: Pass where clause to a function
Date
Msg-id CAA3RN+zLCRbsnzRWcgzhLavFhyNFPsFb8x4WiofzXAuqUKYCbQ@mail.gmail.com
Whole thread Raw
In response to Re: Pass where clause to a function  (John R Pierce <pierce@hogranch.com>)
Responses Re: Pass where clause to a function  (John R Pierce <pierce@hogranch.com>)
Re: Pass where clause to a function  (Alban Hertroys <haramrae@gmail.com>)
List pgsql-general
Tree view
Hi John,

Thanks for the response

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 am open to suggestions here. 

I understand  this type of method will restrict the type predicates that can be written at the top level of the query...  But that is ok, we can work with the restrictions.

I also understand I may have overs simplified the question. So I hope I did not waste your time

Thanks again

Andrew    


On 14 August 2014 15:20, John R Pierce <pierce@hogranch.com> wrote:
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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Pass where clause to a function
Next
From: Patrick Dung
Date:
Subject: Re: Trigger function cannot reference field name with capital letter