Re: [GENERAL] Update from select
em stands for "easy money" update tbl1 set col3=em.col3,col4=em.col4,col5=em.col5 from (select col3, col4,col5 from tbl2 where col1="criteria") em Regards, Justin Tocci Programmer www.workflowproducts.com 7813 Harwood Road North Richland Hills, TX 76180 phone 817-503-9545 skype justintocci On May 13, 2013, at 3:23 PM, Bret Stern wrote: > PG 8.4 > > Having trouble putting together an update query to update > multiple columns in tbl1 from columns in tbl2. > > update tbl1 > set col3,col4,col5 > from > (select col3, col4,col5 from tbl2 where col1="criteria") > > > > Can someone add to the Postgres Docs (shown below) to help me with this. > > UPDATE employees SET sales_count = sales_count + 1 WHERE id = > (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); > > > Many thanks > > Bret Stern > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] APEX / HTML DB for PostgreSQL
Il giorno 24/feb/08, alle ore 04:53, justin tocci ha scritto: In its most basic form a great tool would just start as a pl/pgsql or pl/perl function that could be used to call a table and have it output a batch of records to an editable html form or a colored pdf for nice reports. There would need to be some kind of interface for Apaché of course. I guess the XML functions of 8.3 + an XSL stylesheet should do the job... my 2 cents, e. I'll take a look at them. Anyone have a recommendation for a CGI interface? I've thought about it enough to want to try it. I don't really know how APEX / HTML DB work so I'm open to suggestions. I can either put the controller in a perl module and send intelligent requests for formatted pages to postgres or I could attempt to put a controller into pl/pgsql. It is amazing to me that a similar project doesn't already exist. I am still looking though. I think many people have written functions that output tables into formatted html, Excel and pdf formats. Regards, Justin Tocci www.unirev.com 7813 Harwood Road North Richland Hills, TX 76180 cell 817-988-7758 land 817-503-9545 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] APEX / HTML DB for PostgreSQL
In its most basic form a great tool would just start as a pl/pgsql or pl/perl function that could be used to call a table and have it output a batch of records to an editable html form or a colored pdf for nice reports. There would need to be some kind of interface for Apaché of course. Anyone know of anything like this? Best Regards, Justin Tocci - www.unirev.com 7813 Harwood Road, North Richland Hills, TX 76180 cell 817-988-7758 - land 817-503-9545 -- I am curious to know if there is anything sort of like APEX or Html DB in the Oracle world available for PostgreSQL. I've done a fairly extensive search but this could be done in so many ways I could easily be missing it. Ultimately I am looking to migrate away from MS Access for my front end work and get into something web-based. My main concern is that virtually everything I find wants to take over a large portion of database functionality for Postgres. This can look like caching, relationship management or database abstraction, it all boils down to opportunity for performance degradation to me. When I write a MS Access front end I place all my business logic in the database and use Access strictly for forms, menus, excel exporting and nice looking reports. I'd like a tool that just focused on these items and did them well through the web. I read a bit about APEX/Html DB and how it lived entirely in stored procedures in the database and thought that was a great idea. The logic is right there, not separate from but actually in the database engine, so it shouldn't be tempted to duplicate any database functionality or try to take anything over from the database. So I'm looking for something like that for PostgreSQL. -justin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Primary key column numbers...
What I'm trying to do is IF the relation given is a TABLE, give the primary key column numbers. IF the relation is a VIEW, just give an array with a '1' in it. ELSE NULL. Later I'll put in some hocus-pocus to be more intelligent about VIEWs but right now this would do me fine. I've read the docs on arrays and such, I just can't seem to put together nor cast an array that's compatible with int2vector. There was some notice to look at contrib/array but that has been removed in version 8, which is what I'm using. This is what I have so far. SELECT indkey FROM (SELECT relname, indkey FROM pg_catalog.pg_index join pg_catalog.pg_class ON pg_index.indrelid = pg_class.oid WHERE indisprimary=true UNION SELECT viewname, ('{1}')::int2vector[] as indkey FROM pg_catalog.pg_views ) t WHERE relname = '' Result: ERROR: UNION/INTERSECT/EXCEPT could not convert type int2vector[] to int2vector Any help here would be appreciated. justin tocci fort worth, tx ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] TABLEs to VIEWs with Perl
#!/usr/bin/perl -wuse strict;# This program turns text files with TABLE definitions# clipped from PGADMIN into VIEWs with a pre-pended 't'.## All VIEWs get a full set of rules and permissions and assume the first column is the primary key.# # To use:# put this script into a text file and save it.# make it executable: chmod a+x viewsfromtable.pl# Adjust the INFILE and FINAL directories for your system# put your TABLE files, one TABLE per file into the INFILE directory.# To run: ./viewsfromtable.pl fileone.txt filetwo.txt# Output is appended to view.sql file in FINAL directoryI tried to post the program and got denied for size. Its 72 lines.Is there an appropriate place to post such a thing?justin toccifort worth, texas
[GENERAL] Looking for dependent object DROP and CREATE scripts
I'm looking for a SELECT that I could add a WHERE clause to and get all the CREATE (and seperately, DROP) statements that I need to rebuild dependent objects before I make changes to my tables. For instance, I have a column datatype I want to change in a table. In order to do this I need to drop all the 'first tier' views that depend on it and then re-create them when I'm done, plus I need the 'second tier' views and rules that that depend on the 'first tier' views etc... So if I could just run a couply SELECTs and save the results as .sql scripts I could run, I'd be all set. Rarely the CREATE script would need to be edited to run without error, but it still would be a huge time saver. So essentially I'm looking for two SELECTs (DROP and CREATE) that would hand me the scripts to do this. (Or is there another way?) Just so you don't think I didn't try to figure this out already, I've tried to work with the pg_depend table and this is what I have so far. Read on to see where I'm stuck. Select distinct pg_depend.objid, desc1.relname AS desca, pg_depend.refobjid, desc2.relname AS descb, pg_depend.deptype from pg_depend left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc1 on pg_depend.objid = desc1.oid left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc2 on pg_depend.refobjid = desc2.oid where deptype <> 'p' This gives me the table I'm looking for in column DescB, hooked to its dependent rules in column DescA, but I haven't been able to get dependent views to show up at all. In fact views that depend on views don't show up either. Does pg_depend not have records for dependent views? If not, how is it done? --- justin tocci Fort Wayne, IN
[GENERAL] INSERT RULE QUERY ORDER
When my RULE takes the form of: CREATE RULE name AS ON INSERT TO table DO INSTEAD ( UPDATE query ; INSERT query) ; The INSERT query doesn't fire and there is no error. Putting the INSERT first allows them to both fire. Can anyone tell me why? I think it has something to do with *NEW* and *OLD* being initialized differently for an UPDATE than for an INSERT. I'd like to know what's going on so I can be confident I'm writing my rules correctly. Thanks all. --- jtocci Fort Wayne, IN
Re: [GENERAL] INSERT RULE QUERY ORDER
You are correct Tom, vtquotehistory is a view of tquotehistory, and I am trying to log in this example. Sorry I didn't point that out. The view is a straight view with no WHERE clause or criteria of any kind. The UPDATE does cause the OLD record to become updated to whatever changes have been set as needed in the NEW record. In that sense, the OLD record is discarded, but my code sometimes assumes the keyword OLD is still a valid reference even after an UPDATE. Is that my problem? ARGGHH!!! I just re-read 9.2.1. Read Committed Isolation Level and I am screwed! It was my understanding that all the queries in parenthesis in a RULE were evaluated within a transaction, and that that meant that all queries saw the same snapshot of the database and changes were committed all at once at the end. I see now that queries within a transaction are only ever free from seeing the COMMITs of _other_ transactions, and even that is only for SERIALIZABLE transactions. I've got over twenty rules with multiple updates within them that could be affecting each other in ways I haven't evaluated. I've got my work cut out for me. You're a lifesaver Tom, thanks. --- jtocci Fort Wayne, IN PS - I can't abandon my beautiful rules for triggers just yet :-) With only five pages of code, all rules, I've built a replacement for the DOS application we use (soon to be 'used') to keep track of inventory, shipping/receiving, purchasing and manufacturing('build' from a bill of materials). --- -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Hm. Am I right in supposing that vtquotehistory is a view on tquotehistory? Does the UPDATE cause the row that was visible in the view to be no longer visible in the view (or at least not matched by the constraints on the original UPDATE command)? If so, that's your problem --- the "old" references in the INSERT will no longer find any matching row in the view. If your goal is to log operations on tquotehistory, my recommendation is to forget about views and rules and just use a trigger on tquotehistory. Triggers are *way* easier to understand, even if the notation looks worse. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match