[SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Hello, i have a problem with a trigger written in pl/pgsql. It looks like this: CREATE OR REPLACE FUNCTION versionize() RETURNS TRIGGER AS $$ BEGIN NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; END; $$ LANGUAGE 'plpgsql' VOLATILE; The function should be used at different tables and is invoked before UPDATEs. Everything what happens is the function call of addContentRevision. After this call all data (with the updated revision column) should be stored in the table as a new row. My problem: the aim-table is not static. It's just always the table which invoked the trigger. The trigger itself could be called at many tables. I've tried some other ways of expressing the INSERT but nothing worked: - 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*' - INSERT INTO TG_TABLE_NAME SELECT NEW.* - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW; Do you have any hints? Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Hello, i have a problem with a trigger written in pl/pgsql. It looks like this: CREATE OR REPLACE FUNCTION versionize() RETURNS TRIGGER AS $$ BEGIN NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; END; $$ LANGUAGE 'plpgsql' VOLATILE; The function should be used at different tables and is invoked before UPDATEs. Everything what happens is the function call of addContentRevision. After this call all data (with the updated revision column) should be stored in the table as a new row. My problem: the aim-table is not static. It's just always the table which invoked the trigger. The trigger itself could be called at many tables. I've tried some other ways of expressing the INSERT but nothing worked: - 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*' - INSERT INTO TG_TABLE_NAME SELECT NEW.* - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW; Do you have any hints? Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: >NEW.revision := addContentRevision (OLD.content_id, OLD.revision); >/* not working line, just a stub: >EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; >*/ >RETURN NULL; This seems like the hard way. Why don't you just RETURN NEW and let the normal insertion happen? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; This seems like the hard way. Why don't you just RETURN NEW and let the normal insertion happen? The trigger catches an UPDATE, not an INSERT. I need the old and the new row, because this should emulate revision-control of the content. Greetings, Torsten -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Hello, i have a problem with a trigger written in pl/pgsql. It looks like this: CREATE OR REPLACE FUNCTION versionize() RETURNS TRIGGER AS $$ BEGIN NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; END; $$ LANGUAGE 'plpgsql' VOLATILE; The function should be used at different tables and is invoked after UPDATEs. Everything what happens is the function call of addContentRevision. After this call all data (with the updated revision column) should be stored in the table as a new row. My problem: the aim-table is not static. It's just always the table which invoked the trigger. The trigger itself could be called at many tables. I've tried some other ways of expressing the INSERT but nothing worked: - 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*' - INSERT INTO TG_TABLE_NAME SELECT NEW.* - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW; Do you have any hints? Greetings from Germany, Torsten -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
Torsten, Usually, the “insert ... (select ...)” has the select portion enclosed in parenthesis. Don't know if solution is that simple but did not see it in your examples. It may be worth a try. Stuart - Original message - > Hello, > > i have a problem with a trigger written in pl/pgsql. > > It looks like this: > > CREATE OR REPLACE FUNCTION versionize() > RETURNS TRIGGER > AS $$ > BEGIN > > NEW.revision := addContentRevision (OLD.content_id, OLD.revision); > > /* not working line, just a stub: > EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; > */ > > RETURN NULL; > > END; > $$ LANGUAGE 'plpgsql' VOLATILE; > > The function should be used at different tables and is invoked after > UPDATEs. Everything what happens is the function call of > addContentRevision. After this call all data (with the updated revision > column) should be stored in the table as a new row. > > My problem: the aim-table is not static. It's just always the table > which invoked the trigger. The trigger itself could be called at many > tables. I've tried some other ways of expressing the INSERT but nothing > worked: > - 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT NEW.*' > - INSERT INTO TG_TABLE_NAME SELECT NEW.* > - EXECUTE 'INSERT INTO ' || TG_TABLE_NAME USING NEW; > > Do you have any hints? > > Greetings from Germany, > Torsten > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
[SQL] best paging strategies for large datasets?
Hi, I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and am in the process of developping a pager to let users leaf through it (30K rows). Ideally I'd like to know when requesting any 'page' of data where I am within the dataset: how many pages are available each way, etc. Of course that can be done by doing a count(*) query before requesting a limit/offset subset. But the main query is already quite slow, so I'd like to minimize them. But I am intrigued by window functions, especially the row_number() and ntile(int) ones. Adding "row_number() over (order by )" to my query will return the total number of rows in the first row, letting my deduce the number of pages remaining, etc. row_number() apparently adds very little cost to the main query. And ntile(buckets) seems nice too but I need the total row count for it to contain a 'page' number: ntile(row_count/page_size). What better "paging" strategies are out there? Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] best paging strategies for large datasets?
Hello 2010/5/12 Louis-David Mitterrand : > Hi, > > I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and > am in the process of developping a pager to let users leaf through it > (30K rows). > > Ideally I'd like to know when requesting any 'page' of data where I am > within the dataset: how many pages are available each way, etc. > > Of course that can be done by doing a count(*) query before requesting a > limit/offset subset. But the main query is already quite slow, so I'd > like to minimize them. look on scrollable cursors. see DECLARE CURSOR statement Regards Pavel Stehule > > But I am intrigued by window functions, especially the row_number() and > ntile(int) ones. > > Adding "row_number() over (order by )" to my query will > return the total number of rows in the first row, letting my deduce the > number of pages remaining, etc. row_number() apparently adds very little > cost to the main query. > > And ntile(buckets) seems nice too but I need the total row count for it > to contain a 'page' number: ntile(row_count/page_size). > > What better "paging" strategies are out there? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
On 05/11/10 18:26, Torsten Zühlsdorff wrote: Tom Lane schrieb: =?ISO-8859-15?Q?Torsten_Z=FChlsdorff?= writes: NEW.revision := addContentRevision (OLD.content_id, OLD.revision); /* not working line, just a stub: EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW; */ RETURN NULL; This seems like the hard way. Why don't you just RETURN NEW and let the normal insertion happen? The trigger catches an UPDATE, not an INSERT. I need the old and the new row, because this should emulate revision-control of the content. just set whatever value u want to the fields of the row NEW. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
