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 bret_st...@machinemanagement.com 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 aSELECT 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 makechanges to my tables. For instance, I have acolumn 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 wouldneed to be edited to run without error, but it still would be ahuge 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 methe table I'm looking for in column DescB, hooked to itsdependent 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 tableDO 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
