[ADMIN] View as front-end to function

[Greg Spiegelberg]

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

[Misa Simic]

Hi Greg,

I think it is Foreign Data Wrapper feature what already exist in Postgres

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

So basically, you would need to write your own sourse fdw...

Kind Regards,

Misa

Post by Greg Spiegelberg
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
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