[SQL] inheriting a rule or a trigger?
Hi, I'm trying to protect created_by and created_on columns from accidental update. Most of my tables inherit from a 'source' table that has those columns, so I was thinking of creating a rule or trigger that does nothing on update to these columns. But apparently rules and triggers don't apply to child tables. Is there another way to have the same effect, short of a rule/trigger on each table? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] is there a 'table' data type in pg?
On 24/07/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote: On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: > "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > > > Can I use a another column to store the type of the id_subject (ie: the > > tabled it belongs to) ? Then I would be able to query that table for > > additional info to print alongside the forum posts. > > There are ways to identifier tables in Postgres but there's no way to run a > query against a table using them. Bummer, I suspected as much. > I would strongly recommend you define your own list of "object_types", > probably even have an object_type table with a primary key, a description > column, and a table_name column. Then you can in your application construct > the appropriate query depending on the object_type. Good fallback solution. > One alternative you could do is have a set-returning plpgsql function which > has a big if statement and performs the right kind of query. I think the > records would have to all be the same -- they can't be different kinds of > records depending on the type of object. Will look at that one, always willing to dig deeper into pg's more complex ways :) Thanks for your help, ---(end of broadcast)--- TIP 6: explain analyze is your friend The words table partitioning spring to mind. you could build a view of all the sub tables and then select by tablename='whatever' You may also want to look into inheritance Only some ideas Peter.
Re: [SQL] is there a 'table' data type in pg?
On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote: > "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > > > Can I use a another column to store the type of the id_subject (ie: the > > tabled it belongs to) ? Then I would be able to query that table for > > additional info to print alongside the forum posts. > > There are ways to identifier tables in Postgres but there's no way to run a > query against a table using them. Bummer, I suspected as much. > I would strongly recommend you define your own list of "object_types", > probably even have an object_type table with a primary key, a description > column, and a table_name column. Then you can in your application construct > the appropriate query depending on the object_type. Good fallback solution. > One alternative you could do is have a set-returning plpgsql function which > has a big if statement and performs the right kind of query. I think the > records would have to all be the same -- they can't be different kinds of > records depending on the type of object. Will look at that one, always willing to dig deeper into pg's more complex ways :) Thanks for your help, ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] is there a 'table' data type in pg?
"Louis-David Mitterrand" <[EMAIL PROTECTED]> writes: > Can I use a another column to store the type of the id_subject (ie: the > tabled it belongs to) ? Then I would be able to query that table for > additional info to print alongside the forum posts. There are ways to identifier tables in Postgres but there's no way to run a query against a table using them. I would strongly recommend you define your own list of "object_types", probably even have an object_type table with a primary key, a description column, and a table_name column. Then you can in your application construct the appropriate query depending on the object_type. One alternative you could do is have a set-returning plpgsql function which has a big if statement and performs the right kind of query. I think the records would have to all be the same -- they can't be different kinds of records depending on the type of object. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] is there a 'table' data type in pg?
Hello, In my forum app a new post can be related to several types of objects: person, location, event, simple text subject, etc. so in my 'forum' table I plan to add an id_subject column which can contain a reference to any number of different tables (location, person, etc.). What I need to know is to _what_ table the id_subject belongs. Can I use a another column to store the type of the id_subject (ie: the tabled it belongs to) ? Then I would be able to query that table for additional info to print alongside the forum posts. Thanks for your insights, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Database Synchronization
Hello, Thanks Andrew for the solution. I have successfully installed following rpm << postgresql-slony1-engine-1.1.5-1_PG8.1.4.i686.rpm >> available at http://main.slony.info/downloads/1.1/rpm/. But when I am trying to set a cluster its throwing a following error: :4: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: could not access file "$libdir/xxid": No such file or directory :4: Error: the extension for the xxid data type cannot be loaded in database 'dbname=contactdb host=localhost user=postgres' :4: ERROR: no admin conninfo for node 134590736 I have also tried changing the value of libdir to the directory where xxid.so file is there but couldn't resolve the problem. Thanks, Jyoti Seth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Monday, July 23, 2007 11:43 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Database Synchronization On Mon, Jul 23, 2007 at 02:55:21PM +0530, Jyoti Seth wrote: > In our system postgresql has been installed through YAST. So when we try to > install and configure slony-I through source, it gives the message please > make sure tp build and install postgresql from the sources first. > You _might_ be able to get this to work by installing whatever extra bits the YAST installation tool offers (probably something with -dev- or -src- in it). A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
