[SQL] looking for example of inserting into a view

2008-08-20 Thread EXT-Rothermel, Peter M 
I can't seem to find an example I vaguely remember seeing when I was
originally learning about INSERT rules and views.

This example features a view that is an outer join of several tables.
The example shows how to generate a CSV file of the data in the view
and then loading the data into the original tables from the CSV file.

Does anybody else remember seeing this example?


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] order function in aggregate

2008-08-20 Thread Mike Toews 

Richard Huxton wrote:

Michael Toews wrote:

You could accumulate the values in an array and then sort that with 
the final-func that create aggregate supports.


Thanks for the help. Here was my final functions to aggregate things 
into a comma serpented text element (if anyone is interested):


CREATE OR REPLACE FUNCTION commacat_fn(anyarray)
 RETURNS text AS
$BODY$select array_to_string(sort($1),', ');$BODY$
 LANGUAGE 'sql' IMMUTABLE STRICT
 COST 100;
ALTER FUNCTION commacat_fn(anyarray) OWNER TO postgres;

CREATE AGGREGATE commacat(anyelement) (
 SFUNC=array_append,
 STYPE=anyarray,
 FINALFUNC=commacat_fn,
 INITCOND='{}'
);

---

Lastly a random quick example:

select attrelid, commacat(attname) as attnames from pg_attribute group 
by attrelid order by attrelid;


Certainly there are far better examples that can be used to distill 
information in a table to a comma-separated list.


In some specific applications, a "sort(myarraytype[])" function will 
need to be created if the data type in the aggregate column does not 
have a sort function (fortunately, most data types already have this 
function defined).


Regards,
+mt

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql