Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
On Mon, Dec 01, 2003 at 09:38:06AM +1100, Alex Satrapa wrote: create or replace function get_transactions (INTEGER) returns set of record as ' DECLARE cust_id ALIAS FOR $1; BEGIN for r in select ... from ... loop return next r; end loop; return; END ' language 'plpgsql'; But I would certainly love to have parameterised views :) Me too. I've created many functions to extract data that are joined to other functions. All in all the result is not as optimal as it could be, because the optimizer can not poke into the functions, and the estimates about functions are only guesses. If one could use parametrized views instead of functions the whole mess would probably be more optimal. Maybe there's a TODO here? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Ninguna manada de bestias tiene una voz tan horrible como la humana (Orual) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Alvaro Herrera wrote: Me too. I've created many functions to extract data that are joined to other functions. All in all the result is not as optimal as it could be, because the optimizer can not poke into the functions, and the estimates about functions are only guesses. If one could use parametrized views instead of functions the whole mess would probably be more optimal. How is a parameterized view any different than a set returning SQL function? In either case, you've got the same work to do to teach the optimizer how to understand it, no? Seems like the todo is just that, teach the optimizer how to do better with set-returning SQL functions. Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Was: Triggers, Stored Procedures, PHP
Jason Tesser wrote: [snip] A programmer that doesn't document stuff needs to find a new job :-) This is more of an issue with management. Anyone who does database apps for on any kind of a large scale will tell you that views, triggers, etc.. are essential. I am currently in teh process of writing a complete solution for the college I develop for. Finance, accounting, pos, registration, student tracking etc... I'm going to hop on this thread and ask a question rather than rant (although ranting is fine by me ... rant away). Could someone explain to me the usefulness of views? I understand how they are created. I understand a single query can be created as a view returning all records in a single column of a single table, or maybe even multiple columns across many tables using a complex join. That sounds find if all you want to do is to populate your drop-down list box with selection choices or use the same search criteria each time. But if I want to access certain information for a particular customer that requires joins and the like, then a view would be great. But as far as I know, I am unable to place search parameters into a view. Is this false or am I totally missing the point of views? Shane D ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Shane D [EMAIL PROTECTED] writes: Could someone explain to me the usefulness of views? I understand how they are created. I understand a single query can be created as a view returning all records in a single column of a single table, or maybe even multiple columns across many tables using a complex join. That sounds find if all you want to do is to populate your drop-down list box with selection choices or use the same search criteria each time. But if I want to access certain information for a particular customer that requires joins and the like, then a view would be great. But as far as I know, I am unable to place search parameters into a view. Is this false or am I totally missing the point of views? It's false. You can treat a view just like a table and add clauses to your query that restrict it beyond what the view gives you. I think that's what you're asking about... Views are useful for things like: 1) Insulating apps from details of the schema which may change 2) Giving different users different, well, views of the data, perhaps on a column basis. Create a view that only shows a subset of columns, and only allow unprivileged users access to the view, not the underlying table(s). -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Doug McNaught wrote: It's false. You can treat a view just like a table and add clauses to your query that restrict it beyond what the view gives you. I think that's what you're asking about... Thanks for your reply. I found an example in the postgresql reference manual in the CREATE VIEW section that shows exactly what you said (reproduced below). CREATE VIEW kinds AS SELECT * FROM films WHERE kind = Comedy; The manual uses the view thusly: SELECT * FROM kinds; But what if the films table also had a field for the production company. This implies based on the view definition that it too, has the field (call it prod_co). Could I use the following query to select all Comedy films distributed by the 'Small Company' production company? SELECT * FROM kinds WHERE prod_co = 'Small Company'; Yes this is contribed, but humor me please. Shane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Shane D [EMAIL PROTECTED] writes: But what if the films table also had a field for the production company. This implies based on the view definition that it too, has the field (call it prod_co). Could I use the following query to select all Comedy films distributed by the 'Small Company' production company? SELECT * FROM kinds WHERE prod_co = 'Small Company'; Sure, as long as the column is part of the view, you can use it to constrain the SELECT. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
