On Thu, Jan 12, 2006 at 08:51:44PM -0800, rlee0001 wrote: > 1. > Two new special variables in triggers functions (TG_STATEMENT and > TG_EFFECTIVE_STATEMENT) which returns the statement that triggered the > trigger. Which would that be? The statement that directly invoked the trigger, or the one the user typed, or would you want a list of all of them? > This should be able to be used in row- or statement-level > triggers. For row level triggers I would like TG_EFFECTIVE_STATEMENT to > return the valid statement that operates on that row only. For example > the actual statement: > UPDATE inventory SET status = 0 WHERE status = 1; > ...would be rewritten as: > UPDATE inventory SET status = 0 WHERE id = 2335; > ...when accessed from within a row-level trigger for the row who's > primary key (id) equals 2335. Why, when NEW has all the info you need in a much easier to use format? Seems pretty pointless to me... > 2. > The ability to typecast from boolean to other datatypes. For example: > false::varchar > ...would return varchar 'false' while: > false::integer > ...would return integer 0. Currently there seems to be no way to > typecast from boolean (please correct me if I'm wrong). This is quite > disappointing since you can typecast into boolean. So make them? It's not like it's hard: CREATE CAST (boolean AS varchar) USING FUNCTION bool_to_varchar(bool); > 4. > The ability to view the DDL for objects. Logically I know that this HAS > to be possible already but I can't figure it out and a search of the > documentation doesn't mention it. You can do this in EMS PostgreSQL > Manager but I can't figure out how to query it on my own. psql gives you that. If you give -E it'll even show you the queries it uses to make the info. Also, the information_schema should have most stuff you want. > 5. > The SET and ENUM data types. I know MySQL is cheap and evil but even it > has them. Both are really just Integers attached to some Metadata. You > have no idea how many descriptor tables I have for simple enumerations. > Some have less than 10 items in them! Someone actually mosted a patch that did this. Funnily enough, it'd probably be implemented by creating seperate tables for each ENUM to do the lookup. It's just suger-coating really... > 6. > Cross database queries. I'd like to be able to query a MS SQL Server > database from within PL/PGSQL. Or at least other databases on the same > server. Granted it might not be possible to JOIN, UNION or Subquery > against them but I'd at least like to be able to perform a query and > work with the results. We currently have to feed a postgresql database > daily snapshots the live Microsoft SMS network data using a DTS > package. Being able to access the Live data (especially if we could > join against it) would be awesome. dblink does it for postgres DBs, there are similar modules for connections to other databases. > 8. > The ability to use procedural-language extensions everywhere, not just > in functions. Like where? Give an example. > 9. > The ability to nest fields within fields. For example: > PERSON > NAME > LAST > FIRST > PHONE You can sort of do this, using rowtypes. Havn't nested more than one level though. Not sure why you'd want this though. A database stores data, presentation is the application's job. > 10. > Or an alternative to views where tables can be defined with virtual > fields which point to functions. So for example I can say: > SELECT balance, name FROM customers WHERE balance < 0; > ...where balance actually performs a behind the scenes JOIN against a > transactions table and totals the customers credits and debits. I > realize views can do this but for adding a single dynamic field they > are cumbersome and correct me if I'm wrong but I don't think you can > UPDATE against a view. You are wrong, you can make updatable views. > example. For example: > In: 123 456-7890 > Out: (123) 456-7890 > Stored As: > PHONE = (Virtual Function, with Regexp input parser) > AREA_CODE = 123 > PREFIX = 456 > SUFFIX = 7890 > It would be interesting. Combine with item 9 above and you can make > "name" output in a structured format like "Last, First". Vb.Net's IDE > does this in the properties list for nested properties. So, create a type that does that. PostgreSQL is extensible. It's got data types for ISBNs, Internet addresses and even an XML document type. Compared to that a simple phone number field would be trivial. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment:
signature.asc
Description: Digital signature
