Thread: View as front-end to function
Hi list,
I've done some research on the subject and found only some dated threads on this topic. Wondering if anything has changed.
What I am looking for is a way to create a VIEW that is a front-end to a function. Any conditions in the WHERE clause would be passed as parameters to the function. For example, the query on VIEW queryme_v
SELECT * FROM queryme_v WHERE x=1 AND y='Y';
would result in the function in the VIEW definition being executed as myfunc(1, 'Y'). From what I can tell, the only way to accomplish this today is to simply query the function instead of the VIEW:
SELECT myfunc(1, 'Y') x(c1, c2, c3, ...);
I'm aware of the arguments for using SQL to perform the filter on the results from the VIEW however let me explain my use case. The functions I need to create will act as an interface to an API that acts as a front-end to many large databases. Simply returning everything over possibly slow connections isn't feasible or efficient. The API exists to implement the business logic, filter the data, properly construct the query, decide which database is the target, execute and prevent a flood of information back to the client.
For the sake of argument, let's just say I have the most recent stable PostgreSQL installed. The API is Gdata-like for those of you who may be interested.
Thoughts? Possible with a RULE or a TRIGGER?
-Greg
Hi Greg,
I think it is Foreign Data Wrapper feature what already exist in Postgres
So basically, you would need to write your own sourse fdw...
Kind Regards,
Misa
2012/6/20 Greg Spiegelberg <gspiegelberg@gmail.com>
Hi list,I've done some research on the subject and found only some dated threads on this topic. Wondering if anything has changed.What I am looking for is a way to create a VIEW that is a front-end to a function. Any conditions in the WHERE clause would be passed as parameters to the function. For example, the query on VIEW queryme_vSELECT * FROM queryme_v WHERE x=1 AND y='Y';would result in the function in the VIEW definition being executed as myfunc(1, 'Y'). From what I can tell, the only way to accomplish this today is to simply query the function instead of the VIEW:SELECT myfunc(1, 'Y') x(c1, c2, c3, ...);I'm aware of the arguments for using SQL to perform the filter on the results from the VIEW however let me explain my use case. The functions I need to create will act as an interface to an API that acts as a front-end to many large databases. Simply returning everything over possibly slow connections isn't feasible or efficient. The API exists to implement the business logic, filter the data, properly construct the query, decide which database is the target, execute and prevent a flood of information back to the client.For the sake of argument, let's just say I have the most recent stable PostgreSQL installed. The API is Gdata-like for those of you who may be interested.Thoughts? Possible with a RULE or a TRIGGER?-Greg