rewriting query in PostgreSQL

Question

After a db schema change, what was a column is now computed in stored procedure. Is it possible to make this change seamless for application programs?

So that when a program sends a query like

SELECT id, 
       value 
  FROM table

...it instead gets a result of

SELECT id, 
       compute_value() AS value
  FROM table

I thought I could use a RULE, but it is not possible to create SELECT rule on existing table.

So the only other option seems to me to create a new table and a view with the name of the existing one. Which, because of the need for INSERT/UPDATE triggers for the view is too complicated. Then I'd rather update all the client applications.

Rules are not possible on SELECT? postgresql.org/docs/8.4/interactive/sql-createrule.html Have a look at "event": The event is one of SELECT, INSERT, UPDATE, or DELETE. — DrColossos
– DrColossos, Commented Sep 2, 2010 at 12:50
I wanted "SELECT rule on existing table". Ie. you have a table 'foo' and you want SELECT rule on it. — jira
– jira, Commented Sep 10, 2010 at 12:40

OMG Ponies · Accepted Answer · 2010-08-31 23:38:40Z

2

If you know you want to return value, you use a function rather than a stored procedure. Then you'd reference it like:

SELECT id,
       your_function_name(parameter) AS value
  FROM TABLE

There's an example under "SQL Functions on Composite Types" in the documentation.

Creating a view using the statement above is ideal if your application needs the computed value constantly, otherwise I wouldn't bother.

answered Aug 31, 2010 at 23:38

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frank Heikens Over a year ago

Yep, a VIEW is the way to go.

Collectives™ on Stack Overflow

rewriting query in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related