Re: [SQL] update column based on postgis query on anther table
Sent from my iPhone İ On 16 Tem 2013, îat 08:24, Tom Lane wrote: > Stefan Sylla writes: >> Now I want to use a trigger function to automatically update the column >> 'id_test1_poly' in tabel 'test1_point': > >> /**/ >> create or replace function test1_point_get_id_test1_poly() returns >> trigger as $$ >> begin >> new.id_test1_poly=test1_point_get_id_test1_poly(new.id); >> return new; >> end; >> $$ >> language plpgsql volatile; >> -- create trigger for function: >> create trigger test1_point_get_id_test1_poly >> after insert or update on test1_point for each row execute procedure >> test1_point_get_id_test1_poly(); > > I think you need that to be a BEFORE insert or update trigger. In > an AFTER trigger, it's too late to affect the stored row. > >regards, tom lane > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Listing table definitions by only one command
Hi I would like to list the definition of all user tables by only one command. Is there a way to *not* show pg_catalog tables when using "\d ." in PostgreSQL 9.1.9? Thanks.
Re: [SQL] Listing table definitions by only one command
On Wed, Jul 17, 2013 at 9:29 AM, Carla Goncalves wrote: > Hi > I would like to list the definition of all user tables by only one > command. Is there a way to *not* show pg_catalog tables when using "\d ." > in PostgreSQL 9.1.9? > > Thanks. > I didn't see a way to do that with \ commands, but found this with a google search: SELECT N.nspname, C.relname, A.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) AS typeName FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind='r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE 'public') ORDER BY C.oid, A.attnum; wes
